JOIN CONDITIONS USED IN ORACLE APPS(GL,AP,AR,INV ETC)

JOIN CONDITIONS USED IN ORACLE APPS(GL,AP,AR,INV ETC)
KEY JOINS
GL   AND   APGL_CODE_COMBINATIONS              AP_INVOICES_ALLcode_combination_id             =             acct_pay_code_combination_idGL_CODE_COMBINATIONS              AP_INVOICES_DISTRIBUTIONS_ALLcode_combination_id             =             dist_code_combination_idGL_SETS_OF_BOOKS                         AP_INVOICES_ALLset_of_books_id                   =        set_of_books_id
GL   AND ARGL_CODE_COMBINATIONS                        RA_CUST_TRX_LINE__GL_DIST_ALLcode_combination_id             = code_combination_id
GL   AND INVGL_CODE_COMBINATIONS                        MTL_SYSTEM_ITEMS_Bcode_combination_id             = cost_of_sales_account

GL   AND POGL_CODE_COMBINATIONS                        PO_DISTRIBUTIONS_ALLcode_combination_id             = code_combination_id

PO AND APPO_DISTRIBUTIONS_ALL                       AP_INVOICE_DISTRIBUTIONS_ALLPo_distribution_id                  =                    po_distribution_id
PO_VENDORS                                            AP_INVOICES_ALLvendor_id                             =                    vendor_id
PO AND SHIPMENTSPO_HEADERS_ALL                                           RCV_TRANSACTIONSPo_header_id                         =                        po_header_id
PO_DISTRIBUTIONS_ALL                              RCV_TRANSACTIONSPo_distribution_id                   =             po_distribution_id
SHIPMENTS AND INVOICERCV_TRANSACTIONS AP_INVOICE_DISTRIBUTIONS_ALLRCV_TRANSACTION_ID                  =                            RCV_TRANSACTION_ID

PO AND  INVPO_REQUISITION_LINES_ALL                  MTL_SYSTEM_ITEMS_Bitem_id =           inventory_item_idorg_id                                                 =           organization_id
PO AND HRMSPO_HEADERS_ALL                                           HR_EMPLOYEESAgent_id                                             =             employee_id
PO AND REQUISITIONPO_DISTRIBUTIONS_ALL                                 PO_REQ_DISTRIBUTIONS_ALLreq_distribution_id                          =             distribution_id

SHIPMENTS AND INVRCV_TRANSACTIONS                                     MTL_SYSTEM_ITEMS_BOrganization_id                                =             organization_id
INV AND HRMSMTL_SYSTEM_ITEMS_B                                HR_EMPLOYEESbuyer_id                                              =             employee_id
OM  AND  AROE_ORDER_HEADERS_ALL                          RA_CUSTOMER_TRX_LINES_ALLTO_CHAR( Order_number)                   =   interface_line_attribute1OE_ORDER_LINES_ALL                                RA_CUSTOMER_TRX_LINES_ALLTO_CHAR(Line_id)                                   =   interface_line_attribute6
OE_ORDER_LINES_ALL                          RA_CUSTOMER_TRX_LINES_ALLreference_customer_trx_line_id =   customer_trx_line_id
OM AND SHIPPINGOE_ORDER_HEADERS_ALL        WSH_DELIVARY_DETAILS
HEADER_ID                         =   SOURCE_HEADER_ID
OE_ORDER_HEADERS_ALL        WSH_DELIVARY_DETAILSLINE_ID                        =     SOURCE_LINE_IDAP AND AR (BANKS)AR_CASH_RECEIPTS_ALL AP_BANK_ACCOUNTSREMITTANCE_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_IDAP AND ARHZ_PARTIES                      AP_INVOICES_ALL
PARTY_ID                         =   PARTY_ID
OM AND CRMOE_ORDER_LINES_ALL                 CSI_ITEM_INSTANCES(Install Base)
LINE_ID                           =   LAST_OE_ORDER_LINE_IDTable Name: Po_Requisition_Headers_All AColumn Names Table Name Column NameA. REQUISITION_HEADER_ID PO_REQUISITION_LINES_ALL REQUISITION_HEADER_IDA. TYPE_LOOKUP_CODE PO_DOCUMENT_TYPES DOCUMENT_SUBTYPEA. PREPARER_ID PER_PEOPLE_F PERSON_IDA. ORG_ID MTL_SYSTEM_ITEMS ORGANIZATION_IDA. ORG_ID MTL_ORGANIZATIONS ORGANIZATION_IDTable Name: Po_Requisition_Lines_All BColumn Names Table Name Column NameB .REQUISITION_HEADER_ID PO_REQUISITION_HEADERS_ALL REQUISITION_HEADER_IDB .REQUISITION_LINE_ID PO_REQ_DISTRIBUTIONS_ALL                    REQUISITION_LINE_IDB .LINE_TYPE_ID PO_LINE_TYPES                         LINE_TYPE_IDB .ITEM_ID MTL_SYSTEM_ITEMS                INVENTORY_ITEM_IDB .ORG_ID MTL_SYSTEM_ITEMS                ORGANIZATION_IDTable Name: Po_Requisition_Distributions_All C .Column Names Table Name Column NameC .REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL REQUISITION_LINE_IDC .DISTRIBUTION_ID PO_DISTRIBUTIONS_ALL REQ_DISTRIBUTION_IDC .SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS_IDC .CODE_COMBINATION_ID GL_CODE-COMBINATIONS CODE_COMBINATION_IDTable Name: Po_Distributions_All D .Column Names Table Name Column NameD .PO_LINE_ID PO_LINES PO_LINE_IDD .REQ_DISTRIBUTION_ID PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_IDD .PO_DISTRIBUTION_ID AP_INVOICE_DISTRIBUTIONS_ALL PO_DISTRIBUTION_IDTable Name: Po_Headers_All E .Column Names Table Name Column NameE .PO_HEADER_ID PO_LINES PO_HEADER_IDE .PO_HEADER_ID RCV_SHIPMENT_LINES PO_HEADER_IDE .VENDOR_ID PO_VENDORS VENDOR_IDE .AGENT_ID PER_PEOPLE PERSON_IDE .TYPE_LOOK_UP_CODE PO_DOCUMENT_TYPES DOCUMENT_SUBTYPETable Name: Po_Lines_All F.Column Names Table Name Column NameF.PO_HEADER_ID PO_HEADERS PO_HEADER_IDF.PO_LINE_ID PO_DISTRIBUTIONS_ALL PO_LINE_IDF.ITEM_ID MTL_SYSTEM_ITEMS ITEM_IDTable Name: Rcv_Shipment_Lines G.Column Names Table Name Column NameG.PO_HEADER_ID PO_HEADERS PO_HEADER_IDG.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADERS SHIPMENT_HEADER_IDTable Name: Ap_Invoices_All H.Column Names Table Name Column NameH. INVOICE_ID AP_INVOICE_DISTRIBUTIONS_ALL INVOICE_IDTable Name: Oe_Order_Headers_All I.Column Names Table Name Column NameI.HEADER_ID OE_ORDER_LINES HEADER_IDI.SOURCE_HEADER_ID WISH_DELIVERY_DETAILS SOURCE_HEADER_IDI.PRICE_LIST_ID QP_LIST_HEADERS_TL LIST_HEADER_IDI.ORG_ID MTL_ORGANIZATIONS ORGANIZATION_IDI.SALESREP_ID JTF_RS_SALESREPS SALESREP_IDI.ORDER_TYPE_ID OE_TRANSACTION_TYPES TRANSACTION_TYPE_IDI.ORDER_SOURCE_ID OE_ORDER_SOURCES ORDER_SOURCE_IDI.ACCOUNTING_RULE_ID RA_RULES RULE_IDI.PAYMENT_TERM_ID RA_TERMS TERM_IDI.SOLD_TO_ORG_ID HZ_CUST_ACCOUNTS CUST_ACCOUNT_IDI.SHIP_FROM_ORG_ID MTL_PARAMETERS ORGANIZATION_IDI.SHIP_TO_ORG_ID HZ_CUST_SITE_USES_ALL SITE_USE_IDTable Name: Oe_Order_Lines_All J.Column Names Table Name Column NameJ.LINE_TYPE_ID OE_TRANSACTION_TYPES_TL TRANSACTION_TYPE_IDJ.INVOICING_RULE_ID RA_RULES RULE_IDTable Name: Hz_Parties K.Column Names Table Name Column NameK.PATY_ID HZ_CUST_ACCOUNTS PATY_IDK.CUST_ACCOUNT_ID OE_ORDER_LINES SOLD_TO_ORG_IDTable Name: Hz_Party_Sites_All L.Column Names Table Name Column NameL.PATY_ID HZ_PARTIES PATY_IDL. LOCATION_ID HZ_LOCATIONS LOCATION_IDTable Name: Wsh_delivery_details M.Column Names Table Name Column NameM.SOURCE_HEADER_ID OE_ORDER_HEADERS SOURCE_HEADER_IDM.DELIVERY_DETAIL_ID WSH_DELIVERY_ASSIGNMENTS DELIVERY_DETAIL_IDM.DELIVERY_ID WSH_NEW_DELIVERIES DELIVERY_IDM.INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS INVENTORY_ITEM_IDTable Name: RA_CUSTOMER_TRX_ALL N.Column Names Table Name Column NameN.CUSTOMER_TRX_ID AR_RECEIVABLE_APPLICATIONS_ALL APPLIED_CUSTOMER_TRX_IDN.TERM_ID RA_TERMS TERM_IDN.CUSTOMER_TRX_ID RA_CUST_TRX_LINE_GL_DIST CUSTOMER_TRX_IDTable Name: AR_CASH_REC EIPTS_ALL O.Column Names Table Name Column NameO.CASH_RECEIPT_ID AR_RECEIVABLE_APPLICATIONS_ALL CASH_RECEIPT_IDO.SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS

ORACLE APPS OUTBOUND INTERFACE PROCESS ORACLE APPS OUTBOUND INTERFACE PROCESS

TYPE select * from v$parameter where name like '%utl_file%'

COPY ANY PATH IN THE ABOVE QUERY RESULT FOR OUTBOUND FILE DESTINATION

WE WILL USE THIS PATH IN PROCEDURE

NOTE: ONLY ABOVE QUERY PATHS ARE SUITABLE FOR OUTBOUND FILES

CREATE OUTBOUND PROCEDURE

CREATE OR REPLACE procedure GE_INV_Out_BAL(Errbuf OUT varchar2,

                                       Retcode ouT varchar2,

            f_id    in number,

            t_id    in varchar2) as

cursor c1 is select

    msi.segment1 item,

    msi.inventory_item_id Itemid,

    msi.description  itemdesc,

    msi.primary_uom_code Uom,

    ood.organization_name name,

    ood.organization_id   id,

    mc . segment1||','||mc.segment2 Category

    from

    mtl_system_items_b           msi,

    org_organization_definitions ood,

    mtl_item_categories          mic,

    mtl_categories               mc

    where

    msi.organization_id       = ood.organization_id

    and msi.inventory_item_id = mic.inventory_item_id

    and msi.organization_id   = mic.organization_id

    and mic.category_id       = mc.category_id

    and msi.purchasing_item_flag = 'Y'

    and msi.organization_id between f_id and t_id;

x_id     utl_file.file_type;

l_count  number(5) default 0;

begin

x_id:=utl_file.fopen('c:\temp','invoutdata.txt','W');

--select * from v$parameter where name like '%utl_file%'

for x1 in c1 loop

l_count:=l_count+1;

utl_file.put_line(x_id,x1.item    ||'-'||

                       x1.itemid  ||'-'||

                       x1.itemdesc||'-'||

                       x1.uom   ||'-'||

                       x1.name   ||'-'||

                       x1.id   ||'-'||

                       x1.category   );

end loop;

utl_file.fclose(x_id);

Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'||l_count);

Fnd_File.Put_line(fnd_File.Output,' ');

Fnd_File.Put_line(fnd_File.Output,'Submitted User name  '||Fnd_Profile.Value('USERNAME'));

Fnd_File.Put_line(fnd_File.Output,' ');

Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name '||Fnd_profile.value('RESP_NAME'));

Fnd_File.Put_line(fnd_File.Output,' ');

Fnd_File.Put_line(fnd_File.Output,'Submission Date :'|| SYSDATE);

Exception

WHEN utl_file.invalid_operation THEN

  fnd_file.put_line(fnd_File.log,'invalid operation');

  utl_file.fclose_all;

WHEN utl_file.invalid_path THEN

  fnd_file.put_line(fnd_File.log,'invalid path');

  utl_file.fclose_all;

WHEN utl_file.invalid_mode THEN

  fnd_file.put_line(fnd_File.log,'invalid mode');

  utl_file.fclose_all;

WHEN utl_file.invalid_filehandle THEN

  fnd_file.put_line(fnd_File.log,'invalid filehandle');

  utl_file.fclose_all;

WHEN utl_file.read_error THEN

  fnd_file.put_line(fnd_File.log,'read error');

  utl_file.fclose_all;

WHEN utl_file.internal_error THEN

  fnd_file.put_line(fnd_File.log,'internal error');

  utl_file.fclose_all;

WHEN OTHERS THEN

  fnd_file.put_line(fnd_File.log,'other error');

  utl_file.fclose_all;

End GE_INV_Out_BAL;

RUN ABOVE PROCEDURE SUCCESSFULLY

GOTO SYSTEM ADMINISTRATOR

Click on executable

CLICK ON PERAMETERS BUTTON

SAVE AND COLSE IT THEN GOTO BELOW NAVIGATION

GOTO C/TEMP PATH WE WILL GET FILE

SAVE AND CLOSE IT