Oracle Apps Ocean: January 2014

Wednesday, January 1, 2014

AR INVOICE( XML REPORT)

=================== AR QUERY INVOICE DETAILS ================

SELECT
RCTA.TRX_NUMBER,RCTA.TRX_DATE,RTT.NAME TERMS,RSA.NAME SALES_REP,RCTA.INVOICE_CURRENCY_CODE,
HCA1.ACCOUNT_NAME SOLD_TO_ACCT,HP1.PARTY_NAME SOLD_TO_CUST,
HCA2.ACCOUNT_NAME BILL_TO_ACCT,HP2.PARTY_NAME BILL_TO_CUST,
HL2.ADDRESS1 BILL_ADD1,HL2.ADDRESS2 BILL_ADD2,HL2.ADDRESS3 BILL_ADD3,HL2.ADDRESS4 BILL_ADD4,
HL2.CITY BILL_CITY,HL2.STATE BILL_STATE,HL2.POSTAL_CODE BILL_POST_CODE,FLV2.MEANING BILL_COUNTRY,HCA3.ACCOUNT_NAME SHIP_TO_ACCT,HP3.PARTY_NAME SHIP_TO_CUST,
HL3.ADDRESS1 SHIP_ADD1,HL3.ADDRESS2 SHIP_ADD2,HL3.ADDRESS3 SHIP_ADD3,HL3.ADDRESS4 SHIP_ADD4,
HL3.CITY SHIP_CITY,HL3.STATE SHIP_STATE,HL3.POSTAL_CODE SHIP_POST_CODE,FLV3.MEANING SHIP_COUNTRY,
HL4.ADDRESS1 REMIT_ADD1,HL4.ADDRESS2 REMIT_ADD2,HL4.ADDRESS3 REMIT_ADD3,HL4.ADDRESS4 REMIT_ADD4,
HL4.CITY REMIT_CITY,HL4.STATE REMIT_STATE,HL4.POSTAL_CODE REMIT_POST_CODE,FLV4.MEANING REMIT_COUNTRY,
RCTLA.DESCRIPTION ITEM_DESC,RCTLA.QUANTITY_INVOICED,RCTLA.UNIT_SELLING_PRICE ITEM_PRICE,RCTLA.TAXABLE_AMOUNT,RCTLA.EXTENDED_AMOUNT
FROM
RA_CUSTOMER_TRX_ALL RCTA,
RA_TERMS_TL RTT
,RA_SALESREPS_ALL RSA
,HZ_CUST_ACCOUNTS HCA1
,HZ_PARTIES HP1
,HZ_CUST_ACCOUNTS HCA2
,HZ_PARTIES HP2
,HZ_CUST_SITE_USES_ALL HCAUA2
,HZ_CUST_ACCT_SITES_ALL HCASA2
,HZ_PARTY_SITES HPS2
,HZ_LOCATIONS HL2
,FND_LOOKUP_VALUES FLV2
,HZ_CUST_ACCOUNTS HCA3
,HZ_PARTIES HP3
,HZ_CUST_SITE_USES_ALL HCAUA3
,HZ_CUST_ACCT_SITES_ALL HCASA3
,HZ_PARTY_SITES HPS3
,HZ_LOCATIONS HL3
,FND_LOOKUP_VALUES FLV3
,HZ_PARTY_SITES HPS4
,HZ_LOCATIONS HL4
,FND_LOOKUP_VALUES FLV4
,RA_CUSTOMER_TRX_LINES_ALL RCTLA
WHERE 1=1
AND RCTA.TRX_NUMBER='12024'
AND RCTA.TERM_ID=RTT.TERM_ID
AND RTT.LANGUAGE=USERENV('LANG')
AND RCTA.PRIMARY_SALESREP_ID=RSA.SALESREP_ID
AND RCTA.ORG_ID=RSA.ORG_ID
AND RCTA.SOLD_TO_CUSTOMER_ID=HCA1.CUST_ACCOUNT_ID
AND HCA1.PARTY_ID=HP1.PARTY_ID
AND RCTA.BILL_TO_CUSTOMER_ID=HCA2.CUST_ACCOUNT_ID
AND HCA2.PARTY_ID=HP2.PARTY_ID
AND RCTA.BILL_TO_SITE_USE_ID=HCAUA2.SITE_USE_ID
AND HCAUA2.CUST_ACCT_SITE_ID=HCASA2.CUST_ACCT_SITE_ID
AND HCASA2.PARTY_SITE_ID=HPS2.PARTY_SITE_ID
AND HPS2.LOCATION_ID=HL2.LOCATION_ID
AND HL2.COUNTRY=FLV2.LOOKUP_CODE
AND FLV2.LOOKUP_TYPE='PER_US_COUNTRY_CODE'
AND RCTA.SHIP_TO_CUSTOMER_ID=HCA3.CUST_ACCOUNT_ID
AND HCA3.PARTY_ID=HP3.PARTY_ID
AND RCTA.SHIP_TO_SITE_USE_ID=HCAUA3.SITE_USE_ID
AND HCAUA3.CUST_ACCT_SITE_ID=HCASA3.CUST_ACCT_SITE_ID
AND HCASA3.PARTY_SITE_ID=HPS3.PARTY_SITE_ID
AND HPS3.LOCATION_ID=HL3.LOCATION_ID
AND HL3.COUNTRY=FLV3.LOOKUP_CODE
AND FLV3.LOOKUP_TYPE='PER_US_COUNTRY_CODE'
AND RCTA.REMIT_TO_ADDRESS_ID=HPS4.PARTY_SITE_ID
AND HPS4.LOCATION_ID=HL4.LOCATION_ID
AND HL4.COUNTRY=FLV4.LOOKUP_CODE
AND FLV4.LOOKUP_TYPE='PER_US_COUNTRY_CODE'
AND RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
AND RCTA.ORG_ID=:P_ORG_ID
AND RCTA.TRX_NUMBER BETWEEN NVL(:P_TRX_FROM,RCTA.TRX_NUMBER) AND NVL(:P_TRX_TO,RCTA.TRX_NUMBER)

based on above query generate the following XML Tags

=================== XML TAGS ===================

<?xml version="1.0" encoding="WINDOWS-1252"?>

<FRDARINVHC>
<LIST_G_TRX_NUMBER>
<G_TRX_NUMBER>
<TRX_NUMBER>12024</TRX_NUMBER>
<TRX_DATE>29-SEP-13</TRX_DATE>
<TERMS>Net 15</TERMS>
<SALES_REP>Green, Suzanne</SALES_REP>
<INVOICE_CURRENCY_CODE>USD</INVOICE_CURRENCY_CODE>
<SOLD_TO_ACCT>ABC Application Software</SOLD_TO_ACCT>
<SOLD_TO_CUST>ABC Application Software</SOLD_TO_CUST>
<BILL_TO_ACCT>ABC Application Software</BILL_TO_ACCT>
<BILL_TO_CUST>ABC Application Software</BILL_TO_CUST>
<BILL_ADD1>536 Madison Avenue</BILL_ADD1>
<BILL_ADD2></BILL_ADD2>
<BILL_ADD3></BILL_ADD3>
<BILL_ADD4></BILL_ADD4>
<BILL_CITY>New York</BILL_CITY>
<BILL_STATE>NY</BILL_STATE>
<BILL_POST_CODE>10012</BILL_POST_CODE>
<BILL_COUNTRY>United States of America</BILL_COUNTRY>
<SHIP_TO_ACCT>A. C. Networks</SHIP_TO_ACCT>
<SHIP_TO_CUST>A. C. Networks</SHIP_TO_CUST>
<SHIP_ADD1>3405 East Bay Blvd.</SHIP_ADD1>
<SHIP_ADD2></SHIP_ADD2>
<SHIP_ADD3></SHIP_ADD3>
<SHIP_ADD4></SHIP_ADD4>
<SHIP_CITY>Provo</SHIP_CITY>
<SHIP_STATE>UT</SHIP_STATE>
<SHIP_POST_CODE>84606</SHIP_POST_CODE>
<SHIP_COUNTRY>United States of America</SHIP_COUNTRY>
<REMIT_ADD1>PO Box 680978</REMIT_ADD1>
<REMIT_ADD2>ATTN: Accounts Receivable</REMIT_ADD2>
<REMIT_ADD3>Vision Corporation</REMIT_ADD3>
<REMIT_ADD4></REMIT_ADD4>
<REMIT_CITY>New York</REMIT_CITY>
<REMIT_STATE>NY</REMIT_STATE>
<REMIT_POST_CODE>10022</REMIT_POST_CODE>
<REMIT_COUNTRY>United States of America</REMIT_COUNTRY>
<LIST_G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>10000</EXTENDED_AMOUNT>
<ITEM_PRICE>1000</ITEM_PRICE>
<TAXABLE_AMOUNT></TAXABLE_AMOUNT>
<QUANTITY_INVOICED>10</QUANTITY_INVOICED>
<ITEM_DESC>5" 35 lbs/ft pup joints</ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>475</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>10000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>1900</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>40000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>400</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>40000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>25</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>10000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>100</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>40000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>100</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>10000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>40000</EXTENDED_AMOUNT>
<ITEM_PRICE>2000</ITEM_PRICE>
<TAXABLE_AMOUNT></TAXABLE_AMOUNT>
<QUANTITY_INVOICED>20</QUANTITY_INVOICED>
<ITEM_DESC>Heating/Air Conditioning Filter 24x16x1</ITEM_DESC>
</G_ITEM_DESC>
</LIST_G_ITEM_DESC>
</G_TRX_NUMBER>
</LIST_G_TRX_NUMBER>
</FRDARINVHC>

based on above tags design the following rtf file

==================== INVOICE DESIGN .rtf FILE =====================

HERE ARE SOME SAMPLE DESIGN

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

for variable declaration in xml field
ln1= <?xdoxslt:set_variable($_XDOCTX,'ln',0)?>
for increment of the value of variable
ln2= <?xdoxslt:set_variable($_XDOCTX,'ln', xdoxslt:get_variable($_XDOCTX,'ln')+1)?>
for printing value
ln3= <?xdoxslt:get_variable($_XDOCTX,'ln')?>

for running total
variable declaration
sm1= <?xdoxslt:set_variable($_XDOCTX,'sm',0)?>

for increment of value based on EXTENDED_AMOUNT column value
sm2= <? xdoxslt:set_variable($_XDOCTX,'sm',xdoxslt:get_variable($_XDOCTX,'sm')+EXTENDED_AMOUNT)?>

for printing the value
sm3= <?xdoxslt:get_variable($_XDOCTX,'sm')?>

for total invoice amount

sum = <?sum (EXTENDED_AMOUNT)?>

AP PAYMENT CHECK DETAILS (XML REPORT)

============= DATA TEMPLATE FOR CHECK ==============

<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="AP_INV_CHK" description="ITC CHECK" version="1.0">
<dataQuery>
<sqlStatement name="ITC_CHK_QUERY">
<![CDATA[SELECT AIA.INVOICE_NUM,AIPA.AMOUNT CHECK_AMOUNT,AP_AMOUNT_UTILITIES_PKG.ap_convert_number(AIPA.AMOUNT)||' Rupess Only' AMOUNT,AIPA.BANK_ACCOUNT_NUM,ACA.CHECK_NUMBER,
ACA.VENDOR_NAME,ASSA.VENDOR_SITE_CODE
,ASSA.ADDRESS_LINE1,ASSA.ADDRESS_LINE2,ASSA.ADDRESS_LINE3,ASSA.CITY,ASSA.STATE,ASSA.ZIP,ASSA.PROVINCE,ASSA.COUNTRY
,CBBV.BANK_NAME,CBBV.BANK_BRANCH_NAME
,CBBV.ADDRESS_LINE1 BANK_ADDRESS_LINE1
,CBBV.ADDRESS_LINE2 BANK_ADDRESS_LINE2
,CBBV.ADDRESS_LINE3 BANK_ADDRESS_LINE3
,CBBV.CITY BANK_CITY,CBBV.STATE BANK_STATE,CBBV.ZIP BANK_ZIP,CBBV.PROVINCE BANK_PROVINCE,CBBV.COUNTRY BANK_COUNTRY
FROM
AP_INVOICES_ALL AIA
,AP_INVOICE_PAYMENTS_ALL AIPA
,AP_CHECKS_ALL ACA
,AP_SUPPLIER_SITES_ALL ASSA
,CE_BANK_ACCT_USES_ALL CBAUA
,CE_BANK_ACCOUNTS CBA
,CE_BANK_BRANCHES_V CBBV
WHERE 1=1
AND AIA.INVOICE_NUM='ITC40'
AND AIA.INVOICE_ID=AIPA.INVOICE_ID
AND AIPA.CHECK_ID=ACA.CHECK_ID
AND AIA.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
AND ACA.CE_BANK_ACCT_USE_ID=CBAUA.BANK_ACCT_USE_ID
AND CBAUA.BANK_ACCOUNT_ID=CBA.BANK_ACCOUNT_ID
AND CBA.BANK_BRANCH_ID=CBBV.BRANCH_PARTY_ID]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="ITC_CHECK" source="ITC_CHK_QUERY">
<element name="INVOICE_NUM" value="INVOICE_NUM" />
<element name="CHECK_AMOUNT" value="CHECK_AMOUNT" />
<element name="BANK_ACCOUNT_NUM" value="BANK_ACCOUNT_NUM" />
<element name="CHECK_NUMBER" value="CHECK_NUMBER" />
<element name="VENDOR_NAME" value="VENDOR_NAME" />
<element name="VENDOR_SITE_CODE" value="VENDOR_SITE_CODE" />
<element name="ADDRESS_LINE1" value="ADDRESS_LINE1" />
<element name="ADDRESS_LINE2" value="ADDRESS_LINE2" />
<element name="ADDRESS_LINE3" value="ADDRESS_LINE3" />
<element name="CITY" value="CITY" />
<element name="STATE" value="STATE" />
<element name="ZIP" value="ZIP" />
<element name="PROVINCE" value="PROVINCE" />
<element name="COUNTRY" value="COUNTRY" />
<element name="BANK_NAME" value="BANK_NAME" />
<element name="BANK_BRANCH_NAME" value="BANK_BRANCH_NAME" />
<element name="BANK_ADDRESS_LINE1" value="BANK_ADDRESS_LINE1" />
<element name="BANK_ADDRESS_LINE2" value="BANK_ADDRESS_LINE2" />
<element name="BANK_ADDRESS_LINE3" value="BANK_ADDRESS_LINE3" />
<element name="BANK_CITY" value="BANK_CITY" />
<element name="BANK_STATE" value="BANK_STATE" />
<element name="BANK_ZIP" value="BANK_ZIP" />
<element name="BANK_PROVINCE" value="BANK_PROVINCE" />
<element name="BANK_COUNTRY" value="BANK_COUNTRY" />
</group>
</dataStructure>
</dataTemplate>

based on above data template the following XML Tags are generated.
======================= XML TAGS FOR CHECK DESIGN ====================
<?xml version="1.0" encoding="UTF-8"?>

<AP_INV_CHK>
<LIST_ITC_CHECK>
<ITC_CHECK>
<INVOICE_NUM>ITC40</INVOICE_NUM>
<CHECK_AMOUNT>108.52</CHECK_AMOUNT>
<AMOUNT>One hundred nine Rupess Only</AMOUNT>
<BANK_ACCOUNT_NUM>10271-17621-619</BANK_ACCOUNT_NUM>
<CHECK_NUMBER>1008</CHECK_NUMBER>
<VENDOR_NAME>3M Health Care</VENDOR_NAME>
<VENDOR_SITE_CODE>CORP HQ1</VENDOR_SITE_CODE>
<ADDRESS_LINE1>1000000 3M Way</ADDRESS_LINE1>
<ADDRESS_LINE2/>
<ADDRESS_LINE3/>
<CITY>St. Paul</CITY>
<STATE>MN</STATE>
<ZIP>55144</ZIP>
<PROVINCE/>
<COUNTRY>US</COUNTRY>
<BANK_NAME>Bank of America</BANK_NAME>
<BANK_BRANCH_NAME>New York</BANK_BRANCH_NAME>
<BANK_ADDRESS_LINE1>6788 Wall Street</BANK_ADDRESS_LINE1>
<BANK_ADDRESS_LINE2/>
<BANK_ADDRESS_LINE3/>
<BANK_CITY>New York</BANK_CITY>
<BANK_STATE>New York</BANK_STATE>
<BANK_ZIP>05443</BANK_ZIP>
<BANK_PROVINCE/>
<BANK_COUNTRY>US</BANK_COUNTRY>
</ITC_CHECK>
</LIST_ITC_CHECK>
<LIST_ITC_CHECK>
<ITC_CHECK>
<INVOICE_NUM>ITC40</INVOICE_NUM>
<CHECK_AMOUNT>20000</CHECK_AMOUNT>
<AMOUNT>Twenty Thousands Rupess Only</AMOUNT>
<BANK_ACCOUNT_NUM>10271-17621-619</BANK_ACCOUNT_NUM>
<CHECK_NUMBER>1009</CHECK_NUMBER>
<VENDOR_NAME>Talasila Gowthami</VENDOR_NAME>
<VENDOR_SITE_CODE>CORP HQ</VENDOR_SITE_CODE>
<ADDRESS_LINE1>100 3M Way</ADDRESS_LINE1>
<ADDRESS_LINE2/>
<ADDRESS_LINE3/>
<CITY>St. Paul</CITY>
<STATE>MN</STATE>
<ZIP>55144</ZIP>
<PROVINCE/>
<COUNTRY>US</COUNTRY>
<BANK_NAME>Bank of America</BANK_NAME>
<BANK_BRANCH_NAME>New York</BANK_BRANCH_NAME>
<BANK_ADDRESS_LINE1>6788 Wall Street</BANK_ADDRESS_LINE1>
<BANK_ADDRESS_LINE2/>
<BANK_ADDRESS_LINE3/>
<BANK_CITY>New York</BANK_CITY>
<BANK_STATE>New York</BANK_STATE>
<BANK_ZIP>05443</BANK_ZIP>
<BANK_PROVINCE/>
<BANK_COUNTRY>US</BANK_COUNTRY>
</ITC_CHECK>
</LIST_ITC_CHECK>
</AP_INV_CHK>

based on the above tags design check as below

===================== CHECK DESIGN IN .rtf FILE ======================

HERE ARE SAMPLE DESIGN

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

client has to purchase MICR Code

for free MICR Code refer

http://www.fontspace.com/digital-graphics-labs/micr-encoding

ITEM TRANSACTION INTERFACE

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

/******************************** HERE STAGING TABLE DEFINITION ****************************/

DROP TRIGGER ITC_ITI_T;

DROP SEQUENCE ITC_ITI_S;

DROP TABLE ITC_ITI_STG;

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;

select * from ITC_ITI_STG

select * from mtl_transactions_interface

delete mtl_transactions_interface

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

AND inventory_asset_flag='Y'

AND segment1='Soft_Raghu'

AND organization_id=204;

/***************************** 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 =================================================*/