Oracle Purchasing / Inventory / Payables / GL Query

SELECT DISTINCT A.ORG_ID "ORG ID",
(select organization_name from apps.org_organization_definitions where operating_unit=A.ORG_ID and organization_code='ORG_NAME') org_name,
(SELECT r.segment1 "Req No"
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE c.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
) "PO REQ NUMBER",
(SELECT TO_CHAR(TRUNC(D.CREATION_DATE)) "Req Approved Date"
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE c.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
) "PO REQ APPROVED DATE",
D.SEGMENT1 "PO NUMBER",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE",
G.line_num "PO LINE NUMBER",
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO LINE AMOUNT",
A.INVOICE_NUM "INVOICE NUMBER",
A.INVOICE_AMOUNT "INVOICE AMOUNT",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "INVOICE DATE",
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "Vendor Type",
E.VENDOR_NAME "Vendor Name",
F.VENDOR_SITE_CODE "Vendor Site",
F.ADDRESS_LINE1 "Address",
F.CITY "City",
F.COUNTRY "Country",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO Date ",
D.SEGMENT1 "PO Number ",
(SELECT r.segment1 "Req No"
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE c.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
) "PO Req Number ",
(SELECT TO_CHAR(TRUNC(D.CREATION_DATE)) "Req Approved Date "
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE c.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
) "PO Req Approved Date ",
D.TYPE_LOOKUP_CODE "PO Type",
G.line_num "PO Line Num ",
C.QUANTITY_ORDERED "Qty Ordered",
C.QUANTITY_CANCELLED "QTY Cancelled",
G.ITEM_DESCRIPTION "Item Description",
G.UNIT_PRICE "Unit Price",
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO Line Amount ",
(SELECT SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7||'.'||SEGMENT8 PO_Accrual_Account FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID=c.accrual_account_id) "PO Accrual Account",
(SELECT SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7||'.'||SEGMENT8 PO_variance_Account FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID=c.variance_account_id) "PO Variance Account",
(SELECT
DECODE(PH.APPROVED_FLAG, 'Y', 'Approved')
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) "PO Status",
A.INVOICE_TYPE_LOOKUP_CODE "Invoice Type",
A.INVOICE_AMOUNT "Invoice Amount ",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "Invoice Date ",
A.INVOICE_NUM "Invoice Number ",
(SELECT
DECODE(X.MATCH_STATUS_FLAG, 'A', 'Approved')
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)"Invoice Approved?",
A.AMOUNT_PAID "Amount Paid",
H.AMOUNT "Amount",
I.CHECK_NUMBER "Cheque Number",
TO_CHAR(TRUNC(I.CHECK_DATE)) "Payment Date"
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = 'Y'
Order by 3,5,9,10

let's talk about FNDLOAD utility

FNDLOAD is an API provided by Oracle to download/upload Flexfields Concurrent programs Value sets Menu etc.

Using FNDLOAD, we can download data from an application and upload into another application. Example: I have created a concurrent program in DEV instance and to move that concurrent program into TEST/Production instance I can’t create that program again. So I will download it from DEV instance and upload into TEST instance by using FNDLOAD utility.

Below list of programs can be download/upload with this FNDLOAD API.

1. Concurrent Programs, Executables

2. Request Groups, Request Sets

3. Profile Options

4. Key and Descriptive Flex fields

5. Menus and Responsibilities

6. Forms and Form Functions

7. Attachments

8. Messages

9. Value Sets and Values

10. Lookup Types

11. User Responsibilities

12. Printer Definitions

13. FND Dictionary

14. Help Configuration

15. Document Sequences

16. Concurrent Manager Schedules

The Syntax

FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1.....]

The mode is either DOWNLOAD or UPLOAD.

Note: execute the below command before download/upload fndload’s

In CUSTOM APPLICATION TOP: - . APPSORA.env and then come back to /tmp folder cd /tmp

$ cd / à $ ls à $ cd apptestà $ cd oracle11ià $ cd testapplà $ . APPSORA.env

Example of download:

FNDLOAD apps/pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="XX_TOP" CONCURRENT_PROGRAM_NAME="SHORTNAME"

Example of Upload

FNDLOAD apps/pwd O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct filename.ldt

Sample Script Code for these Objects :

1 - Printer Styles

FNDLOAD apps/pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

2 - Lookups

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE
APPLICATION_SHORT_NAME="prod" LOOKUP_TYPE="lookup name"

3 - Descriptive Flexfield with all of specific Contexts

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX
P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod"
DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context
name"

4 - Key Flexfield Structures

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX
P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SH A_ALL:CVR_ALL:SEG_ALL
? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code"
P_STRUCTURE_CODE="structure name"

5 - Concurrent Programs

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM
APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"

6 - Value Sets

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET
FLEX_VALUE_SET_NAME="value set name"

7 - Value Sets with values

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE
FLEX_VALUE_SET_NAME="value set name"

8 - Profile Options

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE
PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"

9 - Requset Group

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP
REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"

10 - Request Sets

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"

11 - Responsibilities

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY
RESP_KEY="responsibility

12 - Menus

FNDLOAD apps/pwd O Y DOWNLOAD
$FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU
MENU_NAME="menu_name"

13 - Forms/Functions

-- Scripts to DOWNLOAD Form

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct /home/Aitupdev/Ganesh/ICSSFORM.ldt \
FORM FORM_APP_SHORT_NAME='ICIGS' FORM_NAME='ICSSAR%'

-- Scripts to UPLOAD Form
FNDLOAD apps/$APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $ICFND_TOP/bin/ICSSMENU.ldt

-- Scripts to DOWNLOAD Function

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct /home/Aitupdev/Ganesh/ICSSFUNC.ldt
FUNCTION FUNC_APP_SHORT_NAME='ICIGS' FUNCTION_NAME='ICSSAR%'

-- Scripts to UPLOAD Function
FNDLOAD apps/$APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $ICFND_TOP/bin/ICSSMENU.ldt

14. User/Responsibilities

FNDLOAD apps/pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility

15. Forms personalizations

Can be done thru form level or function level.

FNDLOAD apps/pw 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct xxns_poxrqerq_2.ldt FND_FORM_CUSTOM_RULES form_name=POXRQERQ

Or function level

Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt FND_FORM_CUSTOM_RULES function_name="XXFPNAME"

Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt

.lct files list 