Oracle Apps Ocean: 2013

Sunday, December 8, 2013

p2p,o2c Complite Joins

12:27 PM 12/9/2013
-------------------

p2p complite query
-------------------

select prh.segment1 "PO Requisition Number",
pha.segment1 "PO Number",
aps.SEGMENT1 "Supplier Number",
aps.vendor_name,
apss.vendor_site_code,
apsc.first_name,
apsc.last_name,
pla.item_id,
plla.ship_to_organization_id,
plla.ship_to_location_id,
rt.transaction_type,
rt.destination_type_code,
rsh.receipt_num "PO Receipt Number",
aia.invoice_num,
aida.dist_code_combination_id,
aca.check_number,gjh.ledger_id,
gjh.name
from po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
po_line_locations_all plla,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_supplier_contacts apsc,
rcv_transactions rt,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
ap_invoice_payments_all aipa,
ap_checks_all aca,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl
where prh.segment1 = :RequitionNumber --Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
and aps.vendor_id = pha.vendor_id
and apss.vendor_id = aps.vendor_id
and apss.vendor_site_id (+) = pha.vendor_site_id
and apss.vendor_site_id = aca.vendor_site_id
and apsc.vendor_site_id = apss.vendor_site_id
and apsc.vendor_contact_id = pha.vendor_contact_id
and prl.requisition_header_id = prh.requisition_header_id
and prd.requisition_line_id = prl.requisition_line_id
and pda.req_distribution_id = prd.distribution_id
and pla.po_header_id = pda.po_header_id
and pla.po_line_id = pda.po_line_id
and pha.po_header_id = pla.po_header_id
and pha.org_id = 204
and plla.po_header_id = pla.po_header_id
and plla.po_line_id = pla.po_line_id
and rt.transaction_type = 'DELIVER'
and rt.po_header_id = pha.po_header_id
and rt.po_line_id = pla.po_line_id
and rsh.shipment_header_id = rt.shipment_header_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rsl.shipment_line_id = rt.shipment_line_id
and aila.po_header_id = pha.po_header_id
and aila.po_line_id = pla.po_line_id
and aia.invoice_id = aila.invoice_id
and aida.invoice_id = aila.invoice_id
and aida.invoice_line_number = aila.line_number
and aipa.invoice_id = aia.invoice_id
and aca.check_id = aipa.check_id
and xte.entity_code = 'AP_PAYMENTS'
and xte.transaction_number = aca.check_number
and xte.source_id_int_1 = aipa.check_id
and xte.security_id_int_1 = aia.org_id
and xe.entity_id = xte.entity_id
and xah.event_id = xe.event_id
and xal.ae_header_id = xah.ae_header_id
and xal.ae_line_num = aida.invoice_line_number
and xdl.ae_header_id = xah.ae_header_id
and xdl.ae_line_num = xal.ae_line_num
and xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
and gir.reference_5 = xte.entity_id -- Entity Id
and gir.reference_6 = to_char(xe.event_id) --Event Id
and gir.reference_7 = to_char (xah.ae_header_id) -- AE Header Id
and gir.gl_sl_link_id = xal.gl_sl_link_id
--and gir.created_by = 1318
and gjb.je_batch_id = gir.je_batch_id
and gjh.je_batch_id=gjb.je_batch_id
and gjh.je_header_id = gir.je_header_id
and gjl.je_header_id=gjh.je_header_id
and gjl.je_line_num= gir.je_line_num

O2C Complite Query
-----------------

select ooha.order_number,ooha.org_id,
hca.account_name,
hp.party_name "Customer Name",
hcasab.orig_system_reference BILL_TO_ORIG_REF,
hpsb.status BILL_TO_STATUS,
'ADDRESS1 - '||bill_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||bill_loc.address2||','||CHR(10)||
'ADDRESS3 - '||bill_loc.address3||','||CHR(10)|| 'CITY - '||bill_loc.city||','||CHR(10)||
'POSTAL CD- '||bill_loc.postal_code||','||CHR(10)|| 'COUNTRY - '|| bill_loc.country BILL_TO_ADDRESS,
hcasas.orig_system_reference SHIP_TO_ORIG_REF,
hpss.status SHIP_TO_STATUS,
'ADDRESS1 - '||ship_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||ship_loc.address2||','||CHR(10)||
'ADDRESS3 - '||ship_loc.address3||','||CHR(10)|| 'CITY - '||ship_loc.city||','||CHR(10)||
'POSTAL CD- '||ship_loc.postal_code||','||CHR(10)|| 'COUNTRY - '|| ship_loc.country SHIP_TO_ADDRESS,
oola.inventory_item_id,oola.ordered_item,
msib.description item_description,
wnd.name delivery_number,
rct.trx_number "AR Invoice Number",
acr.receipt_number "AR Receipt Number",
gjh.ledger_id,
gjh.name
from oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hpss,
hz_party_sites hpsb,
hz_locations bill_loc,
hz_locations ship_loc,
hz_cust_acct_sites_all hcasab,
hz_cust_acct_sites_all hcasas,
hz_cust_site_uses_all hzsuab,
hz_cust_site_uses_all hzsuas,
mtl_system_items_b msib,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctld,
ar_cash_receipts_all acr,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl
where ooha.order_number = :SalesOrderNumber --Right click :RequitionNumber from Toad Enable Prompt For Substitution

Variables
and ooha.org_id = 204
and hca.cust_account_id = ooha.sold_to_org_id
and hp.party_id = hca.party_id
and hpss.party_id = hca.party_id
and hpsb.party_id = hca.party_id
and bill_loc.location_id = hpss.location_id
and ship_loc.location_id = hpsb.location_id
AND hcasas.cust_account_id = hca.cust_account_id
AND hcasab.cust_account_id = hca.cust_account_id
AND hcasas.party_site_id = hpss.party_site_id
AND hcasab.party_site_id = hpsb.party_site_id
and hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
and hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
and hzsuas.site_use_id = ooha.ship_to_org_id
and hzsuab.site_use_id = ooha.invoice_to_org_id
and wda.delivery_id = wnd.delivery_id(+)
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.source_header_id = ooha.header_id
and wdd.source_line_id = oola.line_id
and wdd.organization_id = msib.organization_id(+)
and wdd.inventory_item_id =msib.inventory_item_id(+)
and rct.interface_header_attribute1 = to_char(ooha.order_number)
and rct.org_id = ooha.org_id
and rctl.customer_trx_id = rct.customer_trx_id
and rctl.sales_order = to_char(ooha.order_number)
and rctld.customer_trx_id = rct.customer_trx_id
and rctld.customer_trx_line_id = rctl.customer_trx_line_id
and acr.receipt_number = 'G-1001'
and acr.pay_from_customer = rct.sold_to_customer_id
and acr.org_id = ooha.org_id
and acr.customer_site_use_id = rct.bill_to_site_use_id
and xte.transaction_number = acr.receipt_number
and xte.entity_code = 'RECEIPTS'
and xe.entity_id = xte.entity_id
and xah.event_id = xe.event_id
and xal.ae_header_id = xah.ae_header_id
and xal.accounting_class_code = 'CASH'
and xdl.ae_header_id = xah.ae_header_id
and xdl.ae_line_num = xal.ae_line_num
--and xdl.source_distribution_id_num_1
and gir.reference_5 = xte.entity_id -- Entity Id
and gir.reference_6 = to_char(xe.event_id) --Event Id
and gir.reference_7 = to_char (xah.ae_header_id) -- AE Header Id
and gir.gl_sl_link_id = xal.gl_sl_link_id
and gir.created_by = 1318
and gjb.je_batch_id = gir.je_batch_id
and gjh.je_batch_id=gjb.je_batch_id
and gjh.je_header_id = gir.je_header_id
and gjl.je_header_id=gjh.je_header_id
and gjl.je_line_num= gir.je_line_num

Sunday, November 3, 2013

Loading valid data into base table(s) without using sql*loader & staging table

USING UTL_FILE LOADING & VALIDATING THE FLAT FILE DATA AND INSERTING INTO BASE TABLES WITH api approche
---------------------------------------------------------------------------------------------------------------

create or replace procedure FRD_SUPP_CONV_PROD
(V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2) is
v_file_type utl_file.file_type;
v_location      VARCHAR2(90):='C:\temp';
v_filename      VARCHAR2(90):='Supplier_Data.txt';
v_open_mode     VARCHAR2(90):='r';
v_data          varchar2(4000);
v_pipe1 number;
v_pipe2 number;
v_pipe3 number;
v_pipe4 number;
v_pipe5 number;
v_VENDOR_NUMBER VARCHAR2(90);
v_VENDOR_NAME VARCHAR2(90);
v_SHIP_TO_LOCATION VARCHAR2(90);
v_BILL_TO_LOCATION VARCHAR2(90);
v_INVOICE_CURRENCY VARCHAR2(90);
v_PAYMENT_CURRENCY VARCHAR2(90);
v_cnt number:=0;
v_valid_flag varchar2(90);
v_dup_cnt number;
v_ship_to_loc_id number;
v_bill_to_loc_id number;
v_invcurr_cnt number:=0;
v_paycurr_cnt number:=0;
x_return_status        VARCHAR2(90);
    x_msg_count         NUMBER;
    x_msg_data        VARCHAR2(90);
p_vendor_rec  AP_VENDOR_PUB_PKG.r_vendor_rec_type;
x_vendor_id   AP_SUPPLIERS.VENDOR_ID%TYPE;
x_party_id   HZ_PARTIES.PARTY_ID%TYPE;
begin
v_file_type:=utl_file.fopen(location       =>v_location,
                 filename     =>v_filename,
                 open_mode    =>v_open_mode);
execute immediate 'truncate table FRD_SUPP_CONV_STG';
loop
begin
v_cnt:=v_cnt+1;
begin
utl_file.get_line(v_file_type,v_data);
dbms_output.put_line(v_data);
v_pipe1:=instr(v_data,'|',1,1);
v_pipe2:=instr(v_data,'|',1,2);
v_pipe3:=instr(v_data,'|',1,3);
v_pipe4:=instr(v_data,'|',1,4);
v_pipe5:=instr(v_data,'|',1,5);
v_vendor_number:=substr(v_data,1,v_pipe1-1);
v_VENDOR_NAME:=substr(v_data,v_pipe1+1,v_pipe2-v_pipe1-1);
v_SHIP_TO_LOCATION:=substr(v_data,v_pipe2+1,v_pipe3-v_pipe2-1);
v_BILL_TO_LOCATION:=substr(v_data,v_pipe3+1,v_pipe4-v_pipe3-1);
v_INVOICE_CURRENCY:=substr(v_data,v_pipe4+1,v_pipe5-v_pipe4-1);
v_PAYMENT_CURRENCY:=substr(v_data,v_pipe5+1);
--DBMS_OUTPUT.PUT_LINE(v_vendor_name || v_valid_flag);
if v_cnt>1 then
/*insert into FRD_SUPP_CONV_STG(VENDOR_NUMBER ,
                               VENDOR_NAME ,
                               SHIP_TO_LOCATION ,
                               BILL_TO_LOCATION ,
                               INVOICE_CURRENCY ,
                               PAYMENT_CURRENCY )
                values(v_VENDOR_NUMBER ,
                               v_VENDOR_NAME ,
                               v_SHIP_TO_LOCATION ,
                               v_BILL_TO_LOCATION ,
                               v_INVOICE_CURRENCY ,
                            v_PAYMENT_CURRENCY ); */
v_valid_flag:='Y';
--DBMS_OUTPUT.PUT_LINE(v_vendor_name || v_valid_flag);
/**************validation for vendor_name starts here*************/
select count(*) into v_dup_cnt
from ap_suppliers
where upper(vendor_name)=upper(v_VENDOR_NAME);
if v_dup_cnt>0 then
v_valid_flag:='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_cnt||'   '||v_vendor_name||'    '||'duplicate vendor');
end if;
/**************validation for vendor_name ends here*************/

/**************validation for invoice currency starts here*************/
select count(*) into v_invcurr_cnt
from fnd_currencies
where upper(currency_code)=upper(v_INVOICE_CURRENCY);
if v_invcurr_cnt=0 then
v_valid_flag:='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_cnt||'   '||v_INVOICE_CURRENCY||'    '||'invalid invoice currency');
end if;
/**************validation for invoice currency ends here*************/
/**************validation for payment currency starts here*************/
select count(*) into v_paycurr_cnt
from fnd_currencies
where upper(currency_code)=upper(v_payment_CURRENCY);
if v_paycurr_cnt=0 then
v_valid_flag:='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_cnt||'   '||v_payment_CURRENCY||'    '||'invalid payment currency');
end if;
/**************validation for invoice currency ends here*************/
DBMS_OUTPUT.PUT_LINE(v_vendor_name || v_valid_flag);
p_vendor_rec.SEGMENT1:=v_vendor_number;
p_vendor_rec.VENDOR_NAME:=v_VENDOR_NAME;
--p_vendor_rec.SHIP_TO_LOCATION_ID:=v_ship_to_loc_id;
--p_vendor_rec.BILL_TO_LOCATION_ID:=v_BILL_to_loc_id;
--p_vendor_rec.INVOICE_CURRENCY_CODE:=v_vendor_number;
--p_vendor_rec.PAYMENT_CURRENCY_CODE:=v_vendor_number;
AP_VENDOR_PUB_PKG.Create_Vendor
(     p_api_version           =>1.0,
      p_init_msg_list        => FND_API.G_TRUE,
    p_commit            => FND_API.G_TRUE,
    p_validation_level    => FND_API.G_VALID_LEVEL_FULL,
    x_return_status       =>x_return_status,
    x_msg_count        =>x_msg_count,
    x_msg_data       =>x_msg_data,
p_vendor_rec  =>p_vendor_rec,
x_vendor_id  =>x_vendor_id,
x_party_id  => x_party_id);
DBMS_OUTPUT.PUT_LINE(v_vendor_name || x_return_status);
COMMIT;
end if;
exception
when no_data_found then
exit;
end;
end;
end loop;
commit;
end LOAD_UTL;
/

Sunday, October 6, 2013

/*========================================FINAL item transaction INTERFACE STARTs here =================================================*/

ITC ITEM TRANSACTION INTERFACE COMPLIET VALIDATIONS WITH ERROR REPORT

CREATE TABLE ITC_ITI_STG(

TRANSACTION_ID NUMBER,

STATUS_FLAG VARCHAR2(2),

ERROR_MESSAGE VARCHAR2(4000),

ORGANIZATION_NAME_LS VARCHAR2(240),

ORGANIZATION_ID NUMBER,

TRANSACTION_DATE_LS DATE,

TRANSACTION_TYPE_NAME_LS VARCHAR2(80),

TRANSACTION_TYPE_ID NUMBER,

ITEM_NAME_LS VARCHAR2(40),

INVENTORY_ITEM_ID NUMBER,

SUBINVENTORY_CODE_LS VARCHAR2(10), -- IF COUNT = 0 THEN INVLAIED CODE

TRANSACTION_UOM_LS VARCHAR2(25),

TRANSACTION_UOM VARCHAR2(3),

TRANSACTION_QUANTITY_LS NUMBER,

DISTRIBUTION_SEGMENT1_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT2_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT3_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT4_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT5_LS VARCHAR2(25),

distribution_account_id number,

SOURCE_CODE VARCHAR2(30), -- FROM HERE FOR OUR PURPOSE

SOURCE_LINE_ID NUMBER,

SOURCE_HEADER_ID NUMBER,

PROCESS_FLAG NUMBER,

TRANSACTION_MODE NUMBER,

LAST_UPDATE_DATE DATE,

LAST_UPDATED_BY NUMBER,

CREATION_DATE DATE,

CREATED_BY NUMBER);

/******************************** HERE SEQUENCE DEFINITION ****************************/

CREATE SEQUENCE ITC_ITI_S;

/******************************** HERE TRIGGER DEFINITION ****************************/

CREATE OR REPLACE TRIGGER ITC_ITI_T

BEFORE INSERT ON ITC_ITI_STG FOR EACH ROW

BEGIN

SELECT ITC_ITI_S.NEXTVAL INTO :NEW.TRANSACTION_ID FROM DUAL;

END ITC_ITI_T;

SELECT * FROM ITC_ITI_STG

truncate table itc_iti_stg

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_DATE_LS,TRANSACTION_TYPE_NAME_LS,

ITEM_NAME_LS,SUBINVENTORY_CODE_LS,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('Vision Operations','28-JUL-2012','Miscellaneous receipt','Soft_Raghu','Stores','Each',10,

'01','520','5250','0000','000')

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_DATE_LS,TRANSACTION_TYPE_NAME_LS,

ITEM_NAME_LS,SUBINVENTORY_CODE_LS,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('Vision Operations','28-JUL-2012','Miscellaneous receipt','Soft_Raghu','Stores','Each',10,

'01','520','5250','0000','000')

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_TYPE_NAME_LS,ITEM_NAME_LS,SUBINVENTORY_CODE_LS

,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('ITC OU','Miscellaneous receipt','Soft_Raghu','Stores','Eah',10,

'01','520','5250','0000','000')

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_TYPE_NAME_LS,ITEM_NAME_LS,SUBINVENTORY_CODE_LS

,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('ORACLE IMAGES','Miscellaneoreceipt','Soft_Raghu','Stores','Each',10,

'01','520','5250','0000','000')

commit;

/***************************** HERE PACKAGE DEFINITION STARTS ***********/

-- version:3-aug-2013

create or replace package ITC_ITI_PKG

procedure MAP(errbuf out varchar2, retcode out number);

procedure VALIDATE(errbuf out varchar2, retcode out number);

procedure LOAD(errbuf out varchar2, retcode out number);

procedure CALL_STDPROG(errbuf out varchar2, retcode out number);

procedure ERROR_REPORT(errbuf out varchar2, retcode out number);

procedure CALL_ALLPRODS(errbuf out varchar2, retcode out number);

end ITC_ITI_PKG;

/***************************** HERE PACKAGE DEFINITION ENDS ***********/

/***************************** HERE PACKAGE BODY DEFINITION STARTS ***********/

create or replace package body ITC_ITI_PKG

procedure MAP(errbuf out varchar2, retcode out number)

begin

/**************** UPDATEING STATING TABLE STARTS HERE ****************/

UPDATE ITC_ITI_STG SET

STATUS_FLAG='N',

ERROR_MESSAGE=NULL,

ORGANIZATION_ID=NULL,TRANSACTION_TYPE_ID=NULL,

INVENTORY_ITEM_ID=NULL,

SOURCE_CODE='LEGACY_RAGHU', --HERE OUR PURPOSE definations

SOURCE_LINE_ID=123,

SOURCE_HEADER_ID=123,

PROCESS_FLAG=1,

TRANSACTION_MODE=3,

LAST_UPDATE_DATE=TRUNC(SYSDATE),

LAST_UPDATED_BY=1318,

CREATION_DATE=TRUNC(SYSDATE),

CREATED_BY=1318;

UPDATE ITC_ITI_STG SET TRANSACTION_DATE_LS=TRUNC(SYSDATE)

WHERE TRANSACTION_DATE_LS IS NULL;

UPDATE ITC_ITI_STG SET

STATUS_FLAG='X',

ERROR_MESSAGE='Duplicate Record'

WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM ITC_ITI_STG

GROUP BY ORGANIZATION_NAME_LS,TRANSACTION_TYPE_NAME_LS,ITEM_NAME_LS,

SUBINVENTORY_CODE_LS);

UPDATE ITC_ITI_STG SET STATUS_FLAG='MM',ERROR_MESSAGE='Organization Name is Required'

WHERE ORGANIZATION_NAME_LS IS NULL;

UPDATE ITC_ITI_STG SET STATUS_FLAG='MM',ERROR_MESSAGE=ERROR_MESSAGE

||',Transaction Type Name is Required' WHERE TRANSACTION_TYPE_NAME_LS IS NULL;

UPDATE ITC_ITI_STG SET STATUS_FLAG='MM',ERROR_MESSAGE=ERROR_MESSAGE

||',Item Name is Requied' WHERE ITEM_NAME_LS IS NULL;

/**************** UPDATEING STATING TABLE ENDS HERE ****************/

COMMIT;

end MAP;

procedure VALIDATE(errbuf out varchar2, retcode out number)

v_organization_id number;

v_date_count number;

v_transaction_type_id number;

v_inventory_item_id number;

v_count number;

v_uom_code varchar2(3);

v_acct_id number;

v_acct_status number;

cursor ORG_CUR is

select organization_name_ls,transaction_id from ITC_ITI_STG where status_flag not in('X' ,'MM')

and organization_name_ls is not null;

cursor TRAN_DATE_CUR is

select transaction_date_ls,transaction_id from ITC_ITI_STG where status_flag not in ('X','MM')

and organization_name_ls is not null;

cursor TRAN_TYPE_CUR is

select transaction_type_name_ls,transaction_id from ITC_ITI_STG where status_flag not in('X' ,'MM')

and organization_name_ls is not null

and transaction_type_name_ls is not null;

cursor ITEM_CUR is

select organization_id,item_name_ls,transaction_id from ITC_ITI_STG where status_flag not in('X' ,'MM')

and organization_name_ls is not null

and item_name_ls is not null;

cursor SUI_CODE_CUR is

select organization_id,subinventory_code_ls,transaction_id from ITC_ITI_STG where status_flag not in('X','MM')

and organization_name_ls is not null;

cursor TRAN_UOM_CUR is

select transaction_uom_ls,transaction_id from ITC_ITI_STG where status_flag not in('X','MM')

and transaction_uom_ls is not null;

cursor ACCT_CUR is

select organization_id,item_name_ls,distribution_segment1_ls,distribution_segment2_ls,distribution_segment3_ls,

distribution_segment4_ls,distribution_segment5_ls,distribution_account_id,

transaction_id from ITC_ITI_STG where status_flag not in('X','MM')

and organization_name_ls is not null and distribution_segment1_ls is not null;

begin

/**************** ORGANIZATION VALIDATION STARTS HERE ****************/

for ORG_REC in ORG_CUR

loop

begin

select organization_id into v_organization_id from ORG_ORGANIZATION_DEFINITIONS

where upper(organization_name)=upper(ORG_REC.organization_name_ls);

update ITC_ITI_STG set organization_id=v_organization_id

where transaction_id=ORG_REC.transaction_id;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE' ,error_message='No Organization exist:'

||organization_name_ls where transaction_id=ORG_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message='Too Many Organizations with :'

||organization_name_ls where transaction_id=ORG_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message='Unexcepted error at Organization:'

||organization_name_ls where transaction_id=ORG_REC.transaction_id;

end;

end loop;

/**************** ORGANIZATION VALIDATION ENDS HERE ****************/

/**************** TRANSACTION DATE VALIDATION STARTS HERE ****************/

for TRAN_DATE_REC in TRAN_DATE_CUR

loop

begin

SELECT COUNT(*) into v_date_count FROM GL_PERIOD_STATUSES WHERE 1=1

AND CLOSING_STATUS='O'

AND TO_CHAR(TRAN_DATE_REC.transaction_date_ls,'YYYY') BETWEEN TO_CHAR(START_DATE,'YYYY')

AND TO_CHAR(END_DATE,'YYYY')

AND TO_CHAR(TRAN_DATE_REC.transaction_date_ls,'MM') BETWEEN TO_CHAR(START_DATE,'MM')

AND TO_CHAR(END_DATE,'MM')

AND TO_CHAR(TRAN_DATE_REC.transaction_date_ls,'DD') BETWEEN TO_CHAR(START_DATE,'DD')

AND TO_CHAR(END_DATE,'DD')

AND APPLICATION_ID in (401,101)

GROUP BY effective_period_num;

if v_date_count =0then

update ITC_ITI_STG set status_flag='VE',error_message=error_message

||'Period is not for: '

||transaction_date_ls where transaction_id=TRAN_DATE_REC.transaction_id;

end if;

exception

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message

||'Un-able check Periods Open/Close for: '

||transaction_date_ls where transaction_id=TRAN_DATE_REC.transaction_id;

end;

end loop;

/**************** TRANSACTION DATE VALIDATION ENDS HERE ****************/

/**************** Transaction Type VALIDATION STARTS HERE****************/

for TRAN_TYPE_REC in TRAN_TYPE_CUR

loop

begin

select transaction_type_id into v_transaction_type_id from MTL_TRANSACTION_TYPES

where upper(transaction_type_name)=upper(TRAN_TYPE_REC.transaction_type_name_ls);

update ITC_ITI_STG set transaction_type_id=v_transaction_type_id

where transaction_id=TRAN_TYPE_REC.transaction_id;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||' ,No transaction type exist:'

||transaction_type_name_ls where transaction_id=TRAN_TYPE_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Too many transaction types:'

||transaction_type_name_ls

where transaction_id=TRAN_TYPE_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Unexpect error at transaction type:'

||transaction_type_name_ls where transaction_id=TRAN_TYPE_REC.transaction_id;

end;

end loop;

/**************** Transaction Type VALIDATION ENDS HERE ****************/

/**************** Item VALIDATION starts HERE ****************/

for ITEM_REC in ITEM_CUR

loop

begin

select inventory_item_id into v_inventory_item_id from MTL_SYSTEM_ITEMS_B

where upper(segment1)=upper(ITEM_REC.item_name_ls)

and organization_id=ITEM_REC.organization_id;

update ITC_ITI_STG set inventory_item_id=v_inventory_item_id

where transaction_id=ITEM_REC.transaction_id and organization_id is not null;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||', No Org/Item combination found:'

||item_name_ls where transaction_id=ITEM_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',No many Items:'

||item_name_ls where transaction_id=ITEM_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Unexcept error at Item:'

||item_name_ls where transaction_id=ITEM_REC.transaction_id;

end;

end loop;

/**************** Item VALIDATION ENDS HERE ****************/

/**************** SubInventory Code VALIDATION starts HERE ****************/

for SUI_CODE_REC in SUI_CODE_CUR

loop

begin

select count(*) into v_count from MTL_SECONDARY_INVENTORIES

where organization_id=SUI_CODE_REC.organization_id

and upper(secondary_inventory_name)=upper(SUI_CODE_REC.subinventory_code_ls);

if v_count=0 then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Invalied org/subinventory:'

||subinventory_code_ls where transaction_id=SUI_CODE_REC.transaction_id;

end if;

end;

end loop;

/**************** SubInventory Code VALIDATION ENDS HERE ****************/

/**************** transaction UOM VALIDATION startsS HERE ****************/

for TRAN_UOM_REC in TRAN_UOM_CUR

loop

begin

select uom_code into v_uom_code from MTL_UNITS_OF_MEASURE_TL

where unit_of_measure=TRAN_UOM_REC.transaction_uom_ls;

update ITC_ITI_STG set transaction_uom=v_uom_code

where transaction_id=TRAN_UOM_REC.transaction_id;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Invalied UOM:'

||transaction_uom_ls where transaction_id=TRAN_UOM_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Too may UOM:'

||transaction_uom_ls where transaction_id=TRAN_UOM_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Unexecpted at UOM:'

||transaction_uom_ls where transaction_id=TRAN_UOM_REC.transaction_id;

end;

end loop;

/**************** transaction UOM VALIDATION endS HERE ****************/

/**************** Accounting VALIDATION starts HERE ****************/

for ACCT_REC in ACCT_CUR

loop

begin

select count(*) into v_acct_status from mtl_sysTEM_ITEMS_B WHERE costing_enabled_flag='Y'

AND inventory_asset_flag='Y'

AND segment1=ACCT_REC.item_name_ls

AND organization_id=ACCT_REC.organization_id;

if v_acct_status>0 then

select code_combination_id into v_acct_id from GL_CODE_COMBINATIONS where

segment1=ACCT_REC.distribution_segment1_ls

and segment2=ACCT_REC.distribution_segment2_ls

and segment3=ACCT_REC.distribution_segment3_ls

and segment4=ACCT_REC.distribution_segment4_ls

and segment5=ACCT_REC.distribution_segment5_ls;

update ITC_ITI_STG set DISTRIBUTION_ACCOUNT_ID=v_acct_id

where transaction_id=ACCT_REC.transaction_id;

end if;

exception

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||'account not found:'

where transaction_id=ACCT_REC.transaction_id;

end;

end loop;

/**************** Accounting VALIDATION ends HERE ****************/

update ITC_ITI_STG set status_flag='V' where status_flag='N' and error_message is null;

COMMIT;

end VALIDATE;

procedure LOAD(errbuf out varchar2, retcode out number)

cursor LOAD_CUR is

select * from ITC_ITI_STG where status_flag='V';

begin

/**************** loding VALIDATION starts HERE ****************/

for LOAD_REC in LOAD_CUR

loop

begin

INSERT INTO MTL_TRANSACTIONS_INTERFACE(

ORGANIZATION_ID,

TRANSACTION_TYPE_ID,

TRANSACTION_DATE,

INVENTORY_ITEM_ID,

SUBINVENTORY_CODE,

TRANSACTION_UOM,

TRANSACTION_QUANTITY,

DISTRIBUTION_ACCOUNT_ID,

SOURCE_CODE,

SOURCE_LINE_ID,

SOURCE_HEADER_ID,

PROCESS_FLAG,

TRANSACTION_MODE,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

CREATION_DATE,

CREATED_BY)

VALUES(load_rec.ORGANIZATION_ID,

load_rec.TRANSACTION_TYPE_ID,

load_rec.TRANSACTION_DATE_ls,

load_rec.INVENTORY_ITEM_ID,

load_rec.SUBINVENTORY_CODE_ls,

load_rec.TRANSACTION_UOM,

load_rec.TRANSACTION_QUANTITY_ls,

load_rec.DISTRIBUTION_ACCOUNT_ID,

load_rec.SOURCE_CODE,

load_rec.SOURCE_LINE_ID,

load_rec.SOURCE_HEADER_ID,

load_rec.PROCESS_FLAG,

load_rec.TRANSACTION_MODE,

load_rec.LAST_UPDATE_DATE,

load_rec.LAST_UPDATED_BY,

load_rec.CREATION_DATE,

load_rec.CREATED_BY);

exception

when others then

update ITC_ITI_STG set status_flag='LE',error_message=error_message

||',loading failed' where transaction_id=LOAD_REC.transaction_id;

end;

end loop;

/**************** loading VALIDATION ends HERE ****************/

commit;

end LOAD;

procedure CALL_STDPROG(errbuf out varchar2, retcode out number)

v_request_id number;

begin

fnd_global.APPS_INITIALIZE(

user_id=>fnd_global.USER_ID,

resp_id=>fnd_global.resp_id,

resp_appl_id=>fnd_global.RESP_APPL_ID);

v_request_id:=fnd_request.SUBMIT_REQUEST(

application =>'INV',

program =>'INCTCM',

description =>'ITC Concurrent Progarm From Package');

commit;

end CALL_STDPROG;

procedure ERROR_REPORT(errbuf out varchar2, retcode out number)

v_count number;

v_valied number;

v_failed number;

v_duplicate number;

cursor ERR_CUR is

select * from ITC_ITI_STG where status_flag not in ('V');

begin

fnd_file.put_line(fnd_file.output,' ITC ITEM TRANSACTION REPORT');

fnd_file.put_line(fnd_file.output,' ---------------------------');

fnd_file.put_line(fnd_file.output,'');

fnd_file.put_line(fnd_file.output,rpad('Orgnization Name', 25,' ')||

rpad('Item Name',15,' ')||

rpad('Transaction Date', 20,' ')||

rpad('Transaction Type Name',40,' ')||

rpad('Error',450,' '));

fnd_file.put_line(fnd_file.output,rpad('----------------', 25,' ')||

rpad('---------',15,' ')||

rpad('----------------', 20,' ')||

rpad('---------------------',40,' ')||

rpad('------',40,' '));

for ERR_REC in ERR_CUR

loop

begin

fnd_file.put_line(fnd_file.output,rpad(ERR_REC.Organization_Name_ls,25,' ')||

rpad(ERR_REC.Item_name_ls,15,' ')||

rpad(ERR_REC.Transaction_date_ls,20,' ')||

rpad(ERR_REC.Transaction_type_name_ls,40,' ')||

rpad(ERR_REC.Error_message,450,' '));

end;

end loop;

fnd_file.put_line(fnd_file.output,' ----------');

fnd_file.put_line(fnd_file.output,' END OF DATA');

fnd_file.put_line(fnd_file.output,' ----------');

fnd_file.put_line(fnd_file.output,'');

fnd_file.put_line(fnd_file.output,' -------');

fnd_file.put_line(fnd_file.output,' SUMMARY');

fnd_file.put_line(fnd_file.output,' -------');

select count(*) into v_count from ITC_ITI_STG;

fnd_file.put_line(fnd_file.output,' Total Number of Records : ' || v_count);

select count(*) into v_valied from ITC_ITI_STG where status_flag='V';

fnd_file.put_line(fnd_file.output,' Number of Valid Records : ' || v_valied);

select count(*) into v_failed from ITC_ITI_STG where statuS_flag='VE' and Error_message is not null;

fnd_file.put_line(fnd_file.output,' Total Number of invalid Records : ' ||v_failed);

select count(*) into v_failed from ITC_ITI_STG where statuS_flag='X' and Error_message is not null;

fnd_file.put_line(fnd_file.output,' Total Number of duplicate Records : ' ||v_failed);

end ERROR_REPORT;

procedure CALL_ALLPRODS(errbuf out varchar2, retcode out number)

v1 varchar2(90);

v2 number;

begin

ITC_ITI_PKG.MAP(V1,V2);

ITC_ITI_PKG.VALIDATE(V1,V2);

ITC_ITI_PKG.LOAD(V1,V2);

ITC_ITI_PKG.CALL_STDPROG(V1,V2);

ITC_ITI_PKG.ERROR_REPORT(V1,V2);

end CALL_ALLPRODS;

end;

/*========================================FINAL item transaction INTERFACE ends here =================================================*/

MAIN PROGRAM

------------------------

ITC INTERFACE FOR ITEM TRANSACTION

CLICK ON :=> View Output

FROM BACK END PROGRAM (STANDARED PROGRM)

-------------------------------------------------------------------------

ITC Concurrent Progarm From Package (Process transaction interface)

CHILD PROGRAM FOR Process transaction interface

-------------------------------------------------------------

Inventory transaction worker