Differences in the Table Suffix

_ALL - Multi org tables. Show data for all orgs. 
_V - Views. Mostly show data specific to the orgs user has logged in. 
_TL - Translation table. Store name, description etc. in mutliple languages. 
_VL - View which selects only one row from _TL table based on the user session's language and org_id. Uses USERENV ('LANG') to fetch data from join of _V and _TL 
_B - Base table. Mostly used for non-org specific data. 

Base CRM Tables

OKC_K_PARTY_ROLES_B
OKC_K_HEADERS_B
OKC_K_LINES_B
OKC_LINE_STYLES_B
OKC_K_LINES_TL
OKC_K_ITEMS
OKC_RULE_GROUPS_B
OKC_REACT_INTERVALS
OKC_TIMEVALUES_B
OKC_COVER_TIMES
OKC_RULES_B
OKS_BUS_PROCESSES_V
OKS_LEVEL_ELEMENTS
OKX_INCIDENT_SEVERITS_V
CSI_ITEM_INSTANCES
HZ_PARTIES
MTL_SYSTEM_ITEMS_B

Replace Function in Oracle SQL

Select Replace(Replace(Description,Chr(10),' '),Chr(13),' ') Desc_Line_Format From Table_Name;

Legal Entity Oracle Apps

Form Compilation Steps in Oracle Apps

Version R12

Step 1: userid / password
Step 2: cd/oracle/apps/apps_st/appl
Step 3: ls *.env
Step 4: cd/oracle/apps/apps_st/appl/au/12.0.0/forms/US
Step 5: frmcmp_batch module=filename.fmb userid=apps/apps model_type=form output_file=/oracle/apps/apps_st/appl/po/12.0.0/forms/US/filename.fmx compile_all=YES

Version 11i

f60gen module=XX_FORM.fmb userid=apps/apps output_file=$TOP/forms/US/SAMPLE.fmx

Oracle PL/SQL : Get Quantity On hand, Reservable Quantity On hand, Quantity Reserved,Quantity Suggested, Quantity Available to Transact, Quantity Available to Reserve

apps.inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => l_api_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => i.organization_id, --Input
p_inventory_item_id => i.inventory_item_id, --Input
p_tree_mode => 1, --apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr);

DBMS_OUTPUT.put_line ('Quantity on hand => '|| TO_CHAR (l_qty_oh));
DBMS_OUTPUT.put_line ('Reservable quantity on hand ===============> '|| TO_CHAR (l_qty_res_oh));
DBMS_OUTPUT.put_line ('Quantity reserved =====================> '|| TO_CHAR (l_qty_res));
DBMS_OUTPUT.put_line ('Quantity suggested ====================> '|| TO_CHAR (l_qty_sug));
DBMS_OUTPUT.put_line ('Quantity Available To Transact => '|| TO_CHAR (l_qty_att));
DBMS_OUTPUT.put_line ('Quantity Available To Reserve => '|| TO_CHAR (l_qty_atr));

R11 to R12 PO And RCV Tables

1 CHV_AUTHORIZATIONS --- CHV_AUTHORIZATIONS
2 CHV_BUCKET_PATTERNS --- CHV_BUCKET_PATTERNS
3 CHV_CUM_ADJUSTMENTS --- CHV_CUM_ADJUSTMENTS
4 CHV_CUM_PERIOD_ITEMS --- CHV_CUM_PERIOD_ITEMS
5 CHV_CUM_PERIODS --- CHV_CUM_PERIODS
6 CHV_HISTORY_CUM_PERIODS --- CHV_HISTORY_CUM_PERIODS
7 CHV_HISTORY_SCHEDULES --- CHV_HISTORY_SCHEDULES
8 CHV_HORIZONTAL_SCHEDULES --- CHV_HORIZONTAL_SCHEDULES
9 CHV_ITEM_ORDERS --- CHV_ITEM_ORDERS
10 CHV_ORG_OPTIONS --- CHV_ORG_OPTIONS
11 CHV_PURGE_CUM_LIST --- CHV_PURGE_CUM_LIST
12 CHV_PURGE_SCHEDULE_LIST --- CHV_PURGE_SCHEDULE_LIST
13 CHV_SCHEDULE_HEADERS --- CHV_SCHEDULE_HEADERS
14 CHV_SCHEDULE_ITEMS --- CHV_SCHEDULE_ITEMS
15 CHV_SCHEDULE_ORGANIZATIONS --- CHV_SCHEDULE_ORGANIZATIONS
16 EDW_PO_VENDOR_HIERARCHIES --- EDW_PO_VENDOR_HIERARCHIES
17 FND_FUNCTION_PARAMETER_MAP --- FND_FUNCTION_PARAMETER_MAP
18 FND_NOTIFICATIONS --- FND_NOTIFICATIONS
19 MLOG$_PO_COMMODITY_CATEGOR --- MLOG$_PO_COMMODITY_CATEGOR
20 MLOG$_PO_VENDOR_SITES_ALL  --- MLOG$_PO_VENDOR_SITES_ALL
21 MLOG$_PO_VENDORS  --- MLOG$_PO_VENDORS
22 PO_ACCEPTANCES --- PO_ACCEPTANCES
23 PO_ACCRUAL_ACCOUNTS_TEMP_ALL --- PO_ACCRUAL_ACCOUNTS_TEMP_ALL
24 PO_ACCRUAL_RECONCILE_TEMP_ALL --- PO_ACCRUAL_RECONCILE_TEMP_ALL
25 PO_ACCRUAL_WRITE_OFFS_ALL --- PO_ACCRUAL_WRITE_OFFS_ALL
26 PO_ACCRUAL_WRITE_OFFS_ALL_EFC --- PO_ACCRUAL_WRITE_OFFS_ALL_EFC
27 PO_ACTION_HISTORY  ---  PO_ACTION_HISTORY
28 PO_ADDRESS_DETAILS_GT --- PO_ADDRESS_DETAILS_GT
29 PO_AGENTS  ---  PO_AGENTS
30 PO_APPROVAL_LIST_HEADERS  ---  PO_APPROVAL_LIST_HEADERS
31 PO_APPROVAL_LIST_LINES  ---  PO_APPROVAL_LIST_LINES
32 PO_APPROVED_SUPPLIER_LIST  ---  PO_APPROVED_SUPPLIER_LIST
33 PO_ASL_ATTRIBUTES  ---  PO_ASL_ATTRIBUTES
34 PO_ASL_DOCUMENTS --- PO_ASL_DOCUMENTS
35 PO_ASL_STATUS_RULES  ---  PO_ASL_STATUS_RULES
36 PO_ASL_STATUSES  ---  PO_ASL_STATUSES
37 PO_ATT_TMP_RECORDS --- PO_ATT_TMP_RECORDS
38 PO_AUTOSOURCE_DOCUMENTS_ALL --- PO_AUTOSOURCE_DOCUMENTS_ALL
39 PO_AUTOSOURCE_RULES --- PO_AUTOSOURCE_RULES
40 PO_AUTOSOURCE_VENDORS --- PO_AUTOSOURCE_VENDORS
41 PO_CHANGE_REQUESTS --- PO_CHANGE_REQUESTS
42 PO_COMMODITIES_B --- PO_COMMODITIES_B
43 PO_COMMODITIES_TL --- PO_COMMODITIES_TL
44 PO_COMMODITY_CATEGORIES --- PO_COMMODITY_CATEGORIES
45 PO_COMMODITY_GRANTS --- PO_COMMODITY_GRANTS
46 PO_COMMUNICATION_GT --- PO_COMMUNICATION_GT
47 PO_CONTROL_FUNCTIONS  ---  PO_CONTROL_FUNCTIONS
48 PO_CONTROL_GROUPS_ALL  ---  PO_CONTROL_GROUPS_ALL
49 PO_CONTROL_RULES  ---  PO_CONTROL_RULES
50 PO_CONTROL_RULES_EFC --- PO_CONTROL_RULES_EFC
51 PO_DISTRIBUTIONS_ALL  ---  PO_DISTRIBUTIONS_ALL
52 PO_DISTRIBUTIONS_ALL_EBS --- PO_DISTRIBUTIONS_ALL_EBS
53 PO_DISTRIBUTIONS_ARCHIVE_ALL  ---  PO_DISTRIBUTIONS_ARCHIVE_ALL
54 PO_DISTRIBUTIONS_GT --- PO_DISTRIBUTIONS_GT
55 PO_DISTRIBUTIONS_INTERFACE --- PO_DISTRIBUTIONS_INTERFACE
56 PO_DOCUMENT_TYPES_ALL_B  ---  PO_DOCUMENT_TYPES_ALL_B
57 PO_DOCUMENT_TYPES_ALL_TL  ---  PO_DOCUMENT_TYPES_ALL_TL
58 PO_EMPLOYEE_HIERARCHIES_ALL  ---  PO_EMPLOYEE_HIERARCHIES_ALL
59 PO_ENCUMBRANCE_GT --- PO_ENCUMBRANCE_GT
60 PO_GA_ORG_ASSIGNMENTS --- PO_GA_ORG_ASSIGNMENTS
61 PO_GA_ORG_ASSIGNMENTS_ARCHIVE --- PO_GA_ORG_ASSIGNMENTS_ARCHIVE
62 PO_HAZARD_CLASSES_B --- PO_HAZARD_CLASSES_B
63 PO_HAZARD_CLASSES_TL --- PO_HAZARD_CLASSES_TL
64 PO_HEADERS_ALL 421 PO_HEADERS_ALL
65 PO_HEADERS_ALL_EBS --- PO_HEADERS_ALL_EBS
66 PO_HEADERS_ARCHIVE_ALL 403 PO_HEADERS_ARCHIVE_ALL
67 PO_HEADERS_GT --- PO_HEADERS_GT
68 PO_HEADERS_INTERFACE --- PO_HEADERS_INTERFACE
69 PO_HISTORY_POS_ALL --- PO_HISTORY_POS_ALL
70 PO_HISTORY_RECEIPTS --- PO_HISTORY_RECEIPTS
71 PO_HISTORY_REQUISITIONS_ALL --- PO_HISTORY_REQUISITIONS_ALL
72 PO_HISTORY_VENDORS --- PO_HISTORY_VENDORS
73 PO_INTERFACE_ERRORS --- PO_INTERFACE_ERRORS
74 PO_JOB_ASSOCIATIONS_B --- PO_JOB_ASSOCIATIONS_B
75 PO_JOB_ASSOCIATIONS_TL --- PO_JOB_ASSOCIATIONS_TL
76 PO_LINE_LOCATIONS_ALL  ---  PO_LINE_LOCATIONS_ALL
77 PO_LINE_LOCATIONS_ALL_EBS --- PO_LINE_LOCATIONS_ALL_EBS
78 PO_LINE_LOCATIONS_ARCHIVE_ALL  ---  PO_LINE_LOCATIONS_ARCHIVE_ALL
79 PO_LINE_LOCATIONS_GT --- PO_LINE_LOCATIONS_GT
80 PO_LINE_TYPES_B  ---  PO_LINE_TYPES_B
81 PO_LINE_TYPES_TL  ---  PO_LINE_TYPES_TL
82 PO_LINES_ALL  ---  PO_LINES_ALL
83 PO_LINES_ALL_EBS --- PO_LINES_ALL_EBS
84 PO_LINES_ARCHIVE_ALL  ---  PO_LINES_ARCHIVE_ALL
85 PO_LINES_GT --- PO_LINES_GT
86 PO_LINES_INTERFACE --- PO_LINES_INTERFACE
87 PO_LOCATION_ASSOCIATIONS_ALL --- PO_LOCATION_ASSOCIATIONS_ALL
88 PO_MASSCANCEL_INCLUDES_ALL --- PO_MASSCANCEL_INCLUDES_ALL
89 PO_MASSCANCEL_INTERIM --- PO_MASSCANCEL_INTERIM
90 PO_MC_DISTRIBUTIONS --- PO_MC_DISTRIBUTIONS
91 PO_MC_HEADERS --- PO_MC_HEADERS
92 PO_NOTE_ATTRIBUTES  ---  PO_NOTE_ATTRIBUTES
93 PO_NOTE_REFERENCES --- PO_NOTE_REFERENCES
94 PO_NOTES --- PO_NOTES
95 PO_NOTIFICATION_CONTROLS --- PO_NOTIFICATION_CONTROLS
96 PO_NOTIFICATIONS_ALL --- PO_NOTIFICATIONS_ALL
97 PO_ONLINE_REPORT_TEXT  ---  PO_ONLINE_REPORT_TEXT
98 PO_ONLINE_REPORT_TEXT_GT --- PO_ONLINE_REPORT_TEXT_GT
99 PO_POSITION_CONTROLS_ALL  ---  PO_POSITION_CONTROLS_ALL
100 PO_PRICE_DIFF_INTERFACE --- PO_PRICE_DIFF_INTERFACE
101 PO_PRICE_DIFFERENTIALS --- PO_PRICE_DIFFERENTIALS
102 PO_PRICE_DIFFERENTIALS_ARCHIVE --- PO_PRICE_DIFFERENTIALS_ARCHIVE
103 PO_PURGE_PO_LIST --- PO_PURGE_PO_LIST
104 PO_PURGE_REQ_LIST --- PO_PURGE_REQ_LIST
105 PO_PURGE_VENDOR_LIST --- PO_PURGE_VENDOR_LIST
106 PO_QUALITY_CODES  ---  PO_QUALITY_CODES
107 PO_QUOTATION_APPROVALS_ALL  ---  PO_QUOTATION_APPROVALS_ALL
108 PO_RELATED_TEMPLATES --- PO_RELATED_TEMPLATES
109 PO_RELEASES_ALL  ---  PO_RELEASES_ALL
110 PO_RELEASES_ALL_EBS --- PO_RELEASES_ALL_EBS
111 PO_RELEASES_ARCHIVE_ALL  ---  PO_RELEASES_ARCHIVE_ALL
112 PO_RELEASES_GT --- PO_RELEASES_GT
113 PO_REQ_DIST_INTERFACE_ALL --- PO_REQ_DIST_INTERFACE_ALL
114 PO_REQ_DISTRIBUTIONS_ALL  ---  PO_REQ_DISTRIBUTIONS_ALL
115 PO_REQ_DISTRIBUTIONS_ALL_EFC --- PO_REQ_DISTRIBUTIONS_ALL_EFC
116 PO_REQ_DISTRIBUTIONS_GT --- PO_REQ_DISTRIBUTIONS_GT
117 PO_REQ_HEADERS_GT --- PO_REQ_HEADERS_GT
118 PO_REQ_LINES_GT --- PO_REQ_LINES_GT
119 PO_REQ_SPLIT_LINES_GT --- PO_REQ_SPLIT_LINES_GT
120 PO_REQEXPRESS_HEADERS_ALL --- PO_REQEXPRESS_HEADERS_ALL
121 PO_REQEXPRESS_LINES_ALL --- PO_REQEXPRESS_LINES_ALL
122 PO_REQUISITION_HEADERS_ALL  ---  PO_REQUISITION_HEADERS_ALL
123 PO_REQUISITION_LINES_ALL  ---  PO_REQUISITION_LINES_ALL
124 PO_REQUISITION_LINES_ALL_EFC --- PO_REQUISITION_LINES_ALL_EFC
125 PO_REQUISITION_SUPPLIERS --- PO_REQUISITION_SUPPLIERS
126 PO_REQUISITIONS_INT_ALL_EFC --- PO_REQUISITIONS_INT_ALL_EFC
127 PO_REQUISITIONS_INTERFACE_ALL --- PO_REQUISITIONS_INTERFACE_ALL
128 PO_RESCHEDULE_INTERFACE --- PO_RESCHEDULE_INTERFACE
129 PO_RETRIEVED_TIMECARDS --- PO_RETRIEVED_TIMECARDS
130 PO_RETROPRICE_GT --- PO_RETROPRICE_GT
131 PO_RFQ_VENDORS  ---  PO_RFQ_VENDORS
132 PO_RULE_EXPENSE_ACCOUNTS --- PO_RULE_EXPENSE_ACCOUNTS
133 PO_SESSION_GT --- PO_SESSION_GT
134 PO_SUPPLIER_ITEM_CAPACITY --- PO_SUPPLIER_ITEM_CAPACITY
135 PO_SUPPLIER_ITEM_TOLERANCE --- PO_SUPPLIER_ITEM_TOLERANCE
136 PO_SYSTEM_PARAMETERS_ALL  ---  PO_SYSTEM_PARAMETERS_ALL
137 PO_UN_NUMBERS_B --- PO_UN_NUMBERS_B
138 PO_UN_NUMBERS_TL --- PO_UN_NUMBERS_TL
139 PO_UNIQUE_IDENTIFIER_CONT_ALL  ---  PO_UNIQUE_IDENTIFIER_CONT_ALL
140 PO_USAGE_ATTRIBUTES  ---  PO_USAGE_ATTRIBUTES
141 PO_USAGES  ---  PO_USAGES
142 PO_VENDOR_CONTACTS  --- PO_VENDOR_CONTACTS
143 PO_VENDOR_LIST_ENTRIES --- PO_VENDOR_LIST_ENTRIES
144 PO_VENDOR_LIST_HEADERS --- PO_VENDOR_LIST_HEADERS
145 PO_VENDOR_SITES_ALL  --- PO_VENDOR_SITES_ALL
146 PO_VENDOR_SITES_ALL_EBS --- PO_VENDOR_SITES_ALL_EBS
147 PO_VENDORS  --- PO_VENDORS
148 PO_VENDORS_EBS --- PO_VENDORS_EBS
149 PO_WF_CANDIDATE_REQ_LINES_TEMP --- PO_WF_CANDIDATE_REQ_LINES_TEMP
150 PO_WF_DEBUG --- PO_WF_DEBUG
151 POA_BIS_SAVINGS --- POA_BIS_SAVINGS
152 POA_BIS_SAVINGS_EFC --- POA_BIS_SAVINGS_EFC
153 POA_BIS_SAVINGS_RPT --- POA_BIS_SAVINGS_RPT
154 POA_BIS_SAVINGS_RPT_EFC --- POA_BIS_SAVINGS_RPT_EFC
155 POA_BIS_SUPPLIER_PERFORMAN_EFC --- POA_BIS_SUPPLIER_PERFORMAN_EFC
156 POA_BIS_SUPPLIER_PERFORMANCE --- POA_BIS_SUPPLIER_PERFORMANCE
157 POA_CM_DISTRIBUTIONS --- POA_CM_DISTRIBUTIONS
158 POA_CM_EVAL_SCORES --- POA_CM_EVAL_SCORES
159 POA_CM_EVALUATION --- POA_CM_EVALUATION
160 RCV_ACCOUNTING_EVENT_TYPES  ---  RCV_ACCOUNTING_EVENT_TYPES
161 RCV_ACCOUNTING_EVENTS  ---  RCV_ACCOUNTING_EVENTS
162 RCV_FTE_TRANSACTION_LINES  ---  RCV_FTE_TRANSACTION_LINES
163 RCV_GAPLESS_INVOICE_NUMBERS --- RCV_GAPLESS_INVOICE_NUMBERS
164 RCV_HEADERS_INTERFACE --- RCV_HEADERS_INTERFACE
165 RCV_LOT_TRANSACTIONS  ---  RCV_LOT_TRANSACTIONS
166 RCV_LOTS_INTERFACE --- RCV_LOTS_INTERFACE
167 RCV_LOTS_SUPPLY --- RCV_LOTS_SUPPLY
168 RCV_MC_REC_SUB_LEDGER --- RCV_MC_REC_SUB_LEDGER
169 RCV_MC_SHIPMENT_HEADERS --- RCV_MC_SHIPMENT_HEADERS
170 RCV_MC_SUB_LEDGER_DETAILS --- RCV_MC_SUB_LEDGER_DETAILS
171 RCV_MC_TRANSACTIONS --- RCV_MC_TRANSACTIONS
172 RCV_PARAMETERS  ---  RCV_PARAMETERS
173 RCV_RECEIVING_SUB_LEDGER  ---  RCV_RECEIVING_SUB_LEDGER
174 RCV_ROUTING_HEADERS_OLD --- RCV_ROUTING_HEADERS_OLD
175 RCV_ROUTING_STEPS --- RCV_ROUTING_STEPS
176 RCV_SERIAL_TRANSACTIONS --- RCV_SERIAL_TRANSACTIONS
177 RCV_SERIALS_INTERFACE --- RCV_SERIALS_INTERFACE
178 RCV_SERIALS_SUPPLY --- RCV_SERIALS_SUPPLY
179 RCV_SHIPMENT_HEADERS  ---  RCV_SHIPMENT_HEADERS
180 RCV_SHIPMENT_LINES  ---  RCV_SHIPMENT_LINES
181 RCV_SHIPMENT_LINES_EFC --- RCV_SHIPMENT_LINES_EFC
182 RCV_SUB_LEDGER_DETAILS --- RCV_SUB_LEDGER_DETAILS
183 RCV_SUPPLY  ---  RCV_SUPPLY
184 RCV_TRANSACTIONS  ---  RCV_TRANSACTIONS
185 RCV_TRANSACTIONS_INTERFACE --- RCV_TRANSACTIONS_INTERFACE
186 RCV_UPGRADE_STEPS --- RCV_UPGRADE_STEPS
187 PO_ATTR_VALUES_ARCHIVE
188 PO_ATTR_VALUES_INTERFACE
189 PO_ATTR_VALUES_TLP_ARCHIVE
190 PO_ATTR_VALUES_TLP_INTERFACE
191 PO_ATTRIBUTE_VALUES
192 PO_ATTRIBUTE_VALUES_DRAFT
193 PO_ATTRIBUTE_VALUES_TLP
194 PO_ATTRIBUTE_VALUES_TLP_DRAFT
195 PO_BC_DISTRIBUTIONS
196 PO_CHANGE_ORDER_TOLERANCES_ALL
197 PO_DISTRIBUTIONS_DRAFT_ALL
198 PO_DOC_STYLE_HEADERS
199 PO_DOC_STYLE_LINES_B
200 PO_DOC_STYLE_LINES_TL
201 PO_DOC_STYLE_VALUES
202 PO_DOCUMENT_TOTALS_GT
203 PO_DRAFTS
204 PO_GA_ORG_ASSIGN_DRAFT
205 PO_HEADERS_DRAFT_ALL
206 PO_LINE_LOCATIONS_DRAFT_ALL
207 PO_LINE_LOCATIONS_INTERFACE
208 PO_LINES_DRAFT_ALL
209 PO_NOTIFICATION_CTRL_DRAFT
210 PO_PRICE_DIFF_DRAFT
211 PO_RCV_CHARGE_ALLOCATIONS
212 PO_RCV_CHARGES
213 PO_VALIDATION_RESULTS_GT
214 PO_VENDOR_CONTACTS_OBS
215 PO_VENDOR_SITES_OBS
216 PO_VENDORS_OBS
217 PO_WF_USER_TMP
218 RCV_CHARGES_INTERFACE
219 RCV_STAGING_TABLE

General Ledger New Tables R12

General Ledger

Table Name

FUN_SEQ_ASSIGNMENTS             Setup Forms and Programs
FUN_SEQ_CONTEXTS             Setup Forms and Programs
FUN_SEQ_ENTITIES             Setup Forms and Programs
FUN_SEQ_HEADERS             Setup Forms and Programs
FUN_SEQ_REQUESTS             Setup Forms and Programs
FUN_SEQ_RULES             Setup Forms and Programs
FUN_SEQ_VERSIONS             Setup Forms and Programs
GL_ACCESS_SET_ASSIGNMENTS             Setup Forms and Programs
GL_ACCESS_SET_NORM_ASSIGN             Setup Forms and Programs
GL_ACCESS_SETS             Setup Forms and Programs
GL_AUTOREV_CRITERIA_SETS             Journal Entry
GL_BC_PACKETS_HISTS             Budgets and related objects
GL_BUDORG_BC_OPTIONS             Budgets and related objects
GL_COA_MAPPINGS             Consolidation and Eliminations
GL_DEFAS_ACCESS_SETS             Setup Forms and Programs
GL_DEFAS_ASSIGNMENTS             Setup Forms and Programs
Table Name             Feature Area
GL_DEFAS_RESP_ASSIGN             Setup Forms and Programs
GL_JE_INCLUSION_RULES             Setup Forms and Programs
GL_JE_LINES_RECON             Reconciliation
GL_JE_SEGMENT_VALUES             Journal Entry
GL_LE_VALUE_SETS             Setup Forms and Programs
GL_LEDGER_CONFIG_DETAILS             Setup Forms and Programs
GL_LEDGER_CONFIGURATIONS             Setup Forms and Programs
GL_LEDGER_NORM_SEG_VALS             Setup Forms and Programs
GL_LEDGER_RELATIONSHIPS             Setup Forms and Programs
GL_LEDGER_SEGMENT_VALUES             Setup Forms and Programs
GL_LEDGER_SET_ASSIGNMENTS             Setup Forms and Programs
GL_LEDGER_SET_NORM_ASSIGN             Setup Forms and Programs
GL_LEDGER_SET_REQUESTS             Setup Forms and Programs
GL_LEDGERS             Setup Forms and Programs
GL_LEGAL_ENTITIES_BSVS             Setup Forms and Programs
GL_MGT_SEG_UPGRADE             Other Issues
GL_MGT_SEG_UPGRADE_H             Other Issues
GL_SEG_VAL_HIERARCHIES             Setup Forms and Programs
GL_SEG_VAL_NORM_HIERARCHY             Setup Forms and Programs
GL_SUMMARY_BC_OPTIONS             Budgets and related objects

Running a Concurrent from your Script

DECLARE
l_success   NUMBER;
l_org_id    NUMBER;
BEGIN
l_org_id = 88;

   DBMS_OUTPUT.put_line ('Initializing');
   fnd_global.apps_initialize (user_id           => 111,
                               resp_id           => 100791,
                               resp_appl_id      => 222
                              );
   DBMS_OUTPUT.put_line ('Begin Submission');
   l_success :=
      fnd_request.submit_request ('AK',
                                  'AKXMOSED',
                                  NULL,
                                  SYSDATE,
                                  FALSE,
                                  l_org_id
                                 );
    DBMS_OUTPUT.put_line ('End Submission '||l_success);
COMMIT;
END;

/***************
fnd_request.submit_request ('FND',                -- application
                                  'COCN_PGM_SHORT_NAME',-- program short name
                                  '',                   -- description
                                  SYSDATE,                   -- start time
                                  FALSE,                -- sub request
                                  'Argument1',          -- argument1
                                  'Argument2',          -- argument2
                                  'N',                  -- argument3
                                  NULL,                 -- argument4
                                  NULL,                 -- argument5
                                  'Argument6',          -- argument6
                                  CHR (0)               -- represents end of arguments
                                 );
**************/

FND_GLOBAL.APPS_INITIALIZE

select fnd.User_id ,
       fresp.Responsibility_id,
       fresp.Responsibility_Name,
       fresp.Application_id
from   fnd_user fnd
,      fnd_responsibility_tl fresp
where  fnd.user_name = 'OPERATIONS'
And    fresp.responsibility_name like 'Vision%'
And    fresp.Language = 'US' ;


fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
                           resp_id=>l_resp_id,
                           resp_appl_id=>l_resp_appl_id);


l_user_id = FND user ID
l_resp_id = responsibility ID
l_resp_appl_id = responsibility application ID 

Unaccounted Receipts with Acct Nature is Average Costing

SELECT DISTINCT jrh.receipt_num, jrh.creation_date, jair.shipment_line_id,
                (SELECT SUM (tax_amount)
                   FROM jai_rcv_line_taxes
                  WHERE shipment_header_id = jrh.shipment_header_id
                    AND shipment_line_id = jair.shipment_line_id
                    AND UPPER (tax_type) = 'EXCISE') excise,
                (SELECT SUM (tax_amount)
                   FROM jai_rcv_line_taxes
                  WHERE shipment_header_id =
                                 jrh.shipment_header_id
                    AND shipment_line_id = jair.shipment_line_id
                    AND UPPER (tax_type) = 'EXCISE_EDUCATION_CESS')
                                                        excise_education_cess,
                (SELECT SUM (tax_amount)
                   FROM jai_rcv_line_taxes
                  WHERE shipment_header_id =
                                    jrh.shipment_header_id
                    AND shipment_line_id = jair.shipment_line_id
                    AND UPPER (tax_type) = 'EXCISE_SH_EDU_CESS')
                                                           excise_sh_edu_cess
           FROM jai_rcv_headers jrh,
                jai_rcv_lines jair,
                jai_rcv_line_taxes jtax
          WHERE jrh.online_claim_flag = 'Y'
            AND jair.shipment_header_id = jrh.shipment_header_id
            AND jair.online_claim_flag = 'Y'
            AND jtax.shipment_header_id = jrh.shipment_header_id
            AND jtax.shipment_line_id = jair.shipment_line_id
            AND jrh.organization_id = :P_ORGN_ID
            AND TRUNC (jrh.creation_date) BETWEEN :p_from_date AND :p_to_date
            --AND jrh.receipt_num = '961664'          -- IN ('961033', '961664')
            AND EXISTS (
                   SELECT '1'
                     FROM jai_rcv_journal_entries
                    WHERE shipment_line_id = jtax.shipment_line_id
                      AND acct_nature = 'Average Costing'
                      AND organization_code =:P_ORGN_CODE
                      AND transaction_type = 'DELIVER'
                      AND TRUNC (transaction_date) BETWEEN :p_from_date
                                                       AND :p_to_date
                      AND jrh.receipt_num NOT IN (
                             SELECT jrj.receipt_num
                               FROM jai_rcv_journal_entries jrj
                              WHERE acct_nature ='CENVAT'
                                AND jrj.organization_code = :P_ORGN_CODE
                                AND transaction_type = 'RECEIVE'
                                -- AND jrj.receipt_num IN (961033, 961664)
                                AND TRUNC (transaction_date)
                                       BETWEEN :p_from_date
                                           AND :p_to_date))
            AND UPPER (jtax.tax_type) IN
                    ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS')

CRM Call Register Query Logic

SELECT DISTINCT jtv.task_id, csivs.creation_date, hou.NAME "Operating Unit",
                TO_CHAR (csivs.creation_date,
                         'DD-MM-YYYY HH24:MI:SSAM'
                        ) "Date & Time",
                hpp.person_name "Call logged by", csstss.NAME "Type of Call",
                csisb.NAME "SR Status", csivs.incident_number "SR Number",
                hcp.phone_number, jtv.attribute8 part_replace,
                jtv.attribute9 service_charge,
                jtv.attribute10 service_charge_in_rs,
                (SELECT user_name
                   FROM fnd_user
                  WHERE user_id = csivs.last_updated_by) modify_by,
                (SELECT user_name
                   FROM fnd_user
                  WHERE user_id = csivs.created_by) created_by,
                hz.party_name "Customer Name",
                (SELECT city
                   FROM hz_locations
                  WHERE location_id =
                           (SELECT a.location_id
                              FROM hz_party_sites a,
                                   cs_incidents_all_b b
                             WHERE a.party_site_id = b.incident_location_id
                               AND b.incident_number = csivs.incident_number))
                                                                     LOCATION,
                (SELECT    address1
                        || ','
                        || address2
                        || ' , '
                        || address3
                        || ' '
                        || city
                        || ' '
                        || state
                   FROM hz_locations
                  WHERE location_id =
                           (SELECT a.location_id
                              FROM hz_party_sites a, cs_incidents_all_b b
                             WHERE a.party_site_id = b.incident_location_id
                               AND b.incident_number = csivs.incident_number))
                                                           "Customer Address",
                (SELECT DISTINCT segment1
                            FROM mtl_system_items_b
                           WHERE inventory_item_id =
                                          csivs.inventory_item_id
                             AND organization_id = csivs.inv_organization_id)
                                                                  "Item Name",
                (SELECT DISTINCT description
                            FROM mtl_system_items_b
                           WHERE inventory_item_id =
                                    csivs.inventory_item_id
                             AND organization_id = csivs.inv_organization_id)
                                                           "Item Description",
                cii.serial_number "Serial Number",
                csivs1.summary "Problem Summary",
                csivs.problem_code "Problem Codes",
                jtv.task_number "Task Number",
                TO_CHAR (jtv.creation_date,
                         'DD-MM-YYYY HH24:MI:SSAM'
                        ) "Task Date",
                jttl.NAME "Task Type", jtl.task_name "Subject",
                jtstl.NAME "Status", jnv.notes "Task Notes",
                TO_CHAR
                     (jtv.planned_start_date,
                      'DD-MM-YYYY HH24:MI:SSAM'
                     ) "Planned Start Date and Time",
                TO_CHAR
                       (jtv.planned_end_date,
                        'DD-MM-YYYY HH24:MI:SSAM'
                       ) "Planned Date End and Time",
                TO_CHAR
                   (jtv.scheduled_start_date,
                    'DD-MM-YYYY HH24:MI:SSAM'
                   ) "Scheduled Start Date and Time",
                TO_CHAR
                     (jtv.scheduled_end_date,
                      'DD-MM-YYYY HH24:MI:SSAM'
                     ) "Scheduled End Date and Time",
                TO_CHAR
                      (jtv.actual_start_date,
                       'DD-MM-YYYY HH24:MI:SSAM'
                      ) "Actual Start Date and Time",
                TO_CHAR (jtv.actual_end_date,
                         'DD-MM-YYYY HH24:MI:SSAM'
                        ) "Actual End Date and Time",
                DECODE (jtv.task_number,
                        xx_mrt1_no (csivs.incident_id), xx_mrt1
                                                            (csivs.incident_id),
                        NULL
                       ) "MTR1",
                DECODE (jtv.task_number,
                        xx_mrt2_no (csivs.incident_id), xx_mrt2
                                                            (csivs.incident_id),
                        NULL
                       ) "MTR2",
                jtv.attribute1 "Report Signed By",
                jtv.attribute2 "Contact No", jtv.attribute3 "Report Number",
                jtv.attribute4 "Report Status",
                jtv.attribute5 "Customer Remark",
                jtv.attribute6 "Engineer Remark",
                jtptl.description "Priority",
                TO_CHAR (okh.start_date,
                         'DD-MM-YYYY HH24:MI:SSAM'
                        ) "Warranty Start Date",
                TO_CHAR (okh.end_date,
                         'DD-MM-YYYY HH24:MI:SSAM'
                        ) "Warranty End date"
FROM            cs_incidents_b_sec csivs,
                hz_parties hz,
                hr_operating_units hou,
                cs_incident_statuses_tl csisb,
                cs_incident_types_tl csstss,
                jtf_tasks_b jtv,
                jtf_task_types_tl jttl,
                jtf_tasks_tl jtl,
                jtf_task_statuses_tl jtstl,
                jtf_task_priorities_tl jtptl,
                jtf_notes_vl jnv,
                jtf_task_all_assignments jtaa,
                jtf_rs_resource_extns jrd,
                csi_item_instances cii,
                cs_incidents_all_tl csivs1,
                cs_hz_sr_contact_points chsc,
                hz_person_profiles hpp,
                okc_k_headers_all_b okh,
                hz_party_sites hzp,
                hz_locations hzl,
                fnd_user fs,
                hz_contact_points hcp
WHERE           TRUNC (csivs.creation_date)
                   BETWEEN NVL (:xx_dat_from, TRUNC (csivs.creation_date))
                       AND NVL (:xx_dat_to, TRUNC (csivs.creation_date))
            AND hou.organization_id = NVL (:xx_og, hou.organization_id)
            AND hz.party_id = NVL (:xx_pn, hz.party_id)
            AND csivs.incident_number = NVL (:xx_in, csivs.incident_number)
            AND (csivs.created_by = :xx_owner OR :xx_owner IS NULL)
            AND (cii.serial_number = (:xx_sno) OR (:xx_sno IS NULL))
            AND (csivs.inventory_item_id = :xx_iid OR :xx_iid IS NULL)
            AND csivs.incident_type_id =
                                       NVL (:xx_it_id, csivs.incident_type_id)
            AND csivs.incident_status_id =
                                     NVL (:xx_it_ty, csivs.incident_status_id)
            AND (csivs.problem_code = :xx_pc OR :xx_pc IS NULL)
            AND (   jtv.task_priority_id =
                                        NVL (:xx_t_prio, jtv.task_priority_id)
                 OR :xx_t_prio IS NULL
                )
            AND (   jtv.task_type_id = NVL (:xx_t_type, jtv.task_type_id)
                 OR :xx_t_type IS NULL
                )
            AND (   jtv.task_status_id =
                                        NVL (:xx_t_status, jtv.task_status_id)
                 OR :xx_t_status IS NULL
                )
            AND (   jtf_task_utl.get_owner (jtv.owner_type_code, jtv.owner_id) =
                                                                   :xx_j_owner
                 OR :xx_j_owner IS NULL
                )
            AND (jrd.resource_id = :xx_ass OR :xx_ass IS NULL)
            AND (   TRUNC (jtv.scheduled_start_date) =
                           NVL (:xx_sc_from, TRUNC (jtv.scheduled_start_date))
                 OR :xx_sc_from IS NULL
                )
            AND (   TRUNC (jtv.scheduled_end_date) =
                               NVL (:xx_sc_to, TRUNC (jtv.scheduled_end_date))
                 OR :xx_sc_to IS NULL
                )
            AND csivs.customer_id = hz.party_id
            AND csivs.org_id = hou.organization_id
            AND csivs.incident_status_id = csisb.incident_status_id
            AND csivs.incident_type_id = csstss.incident_type_id
            AND jtv.source_object_id(+) = csivs.incident_id
            AND jtv.task_type_id = jttl.task_type_id(+)
            AND jtv.task_id = jtl.task_id(+)
            AND jtv.task_status_id = jtstl.task_status_id(+)
            AND jtv.task_priority_id = jtptl.task_priority_id(+)
            AND jtv.task_number = jnv.source_number(+)
            AND jtv.task_id = jtaa.task_id(+)
            AND (   jtaa.assignee_role IN ('ASSIGNEE', 'OWNER')
                 OR jtaa.assignee_role IS NULL
                )
            AND jtaa.resource_id = jrd.resource_id(+)
            AND csivs.incident_id = csivs1.incident_id
            AND csivs1.LANGUAGE = USERENV ('LANG')
            AND csivs.customer_product_id = cii.instance_id(+)
            AND csivs.contract_id = okh.ID(+)
            AND chsc.incident_id(+) = csivs.incident_id
            AND (chsc.primary_flag = 'Y' OR chsc.primary_flag IS NULL)
            AND (chsc.party_id - 1) = hpp.party_id(+)
            AND hzp.party_site_id(+) = csivs.ship_to_site_id
            AND hzp.location_id = hzl.location_id(+)
            AND hcp.contact_point_id(+) = chsc.contact_point_id
            AND csivs.created_by = fs.user_id
       ORDER BY csivs.creation_date, jtv.task_id

Rupees to Words PL/SQL

CREATE OR REPLACE FUNCTION APPS.ruppee_to_word100 (amount IN NUMBER)
   RETURN VARCHAR2
AS
   v_length   INTEGER         := 0;
   v_num2     VARCHAR2 (50)   := NULL;
   v_amount   VARCHAR2 (50)   := TO_CHAR (TRUNC (amount));
   v_word     VARCHAR2 (4000) := NULL;
   v_word1    VARCHAR2 (4000) := NULL;

   TYPE myarray IS TABLE OF VARCHAR2 (255);

   v_str      myarray         := myarray (' Thousand ', ' Lakh ', ' Crore ');
BEGIN
   IF ((amount = 0) OR (amount IS NULL))
   THEN
      v_word := 'zero';
   ELSIF (TO_CHAR (amount) LIKE '%.%')
   THEN
      IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0)
      THEN
         v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);

         IF (LENGTH (v_num2) < 2)
         THEN
            v_num2 := v_num2 * 10;
         END IF;

         v_word1 :=
               'Rupees AND '
            || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1, 2),
                                  'J'),
                         'JSP'
                        )
               )
            || ' paise ';
         v_amount := SUBSTR (amount, 1, INSTR (amount, '.') - 1);
         v_word :=
               TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2, 3),
                                 'J'
                                ),
                        'Jsp'
                       )
            || v_word;
         v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);

         FOR i IN 1 .. v_str.COUNT
         LOOP
            EXIT WHEN (v_amount IS NULL);
            v_word :=
                  TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,
                                            2),
                                    'J'
                                   ),
                           'Jsp'
                          )
               || v_str (i)
               || v_word;
            v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);
         END LOOP;
      END IF;
   ELSE
      v_word := TO_CHAR (TO_DATE (TO_CHAR (amount, '999999999'), 'J'), 'JSP');
   END IF;

   v_word := v_word || ' ' || v_word1 || ' only ';
   v_word := REPLACE (RTRIM (v_word), ' ', ' ');
   v_word := REPLACE (RTRIM (v_word), '-', ' ');
   RETURN INITCAP (v_word);
END ruppee_to_word100;
/

WIP Query

SELECT   we.wip_entity_name job, ood.organization_name organization_name,
         msib1.segment1 fg_item,
         (SELECT stragg (DISTINCT c.item_cost)
            FROM apps.cst_item_costs c
           WHERE c.inventory_item_id = msib1.inventory_item_id
             AND c.organization_id = msib1.organization_id
             ) fg_cost,
         wro.operation_seq_num operation, bso.operation_code,
         bso.operation_description, wro.quantity_issued qty_completed,
         msib.segment1 rm_item, msib.description rm_desc,
         cic.item_cost rm_cost,
         (wro.quantity_issued * cic.item_cost) consumed_rm_cost
    FROM apps.wip_entities we,
         apps.org_organization_definitions ood,
         apps.wip_requirement_operations wro,
         apps.mtl_system_items_b msib,
         apps.mtl_system_items_b msib1,
         apps.cst_item_costs cic,
         apps.wip_operations wo,
         apps.bom_standard_operations bso
   WHERE 1 = 1
     AND we.organization_id = ood.organization_id
     AND wro.wip_entity_id = we.wip_entity_id
     AND wro.organization_id = we.organization_id
     AND msib.inventory_item_id = wro.inventory_item_id
     AND msib.organization_id = wro.organization_id
     AND msib1.inventory_item_id = we.primary_item_id
     AND msib1.organization_id = we.organization_id
     AND cic.inventory_item_id = msib.inventory_item_id
     AND cic.organization_id = msib.organization_id
     AND bso.standard_operation_id(+) = wo.standard_operation_id
     AND wo.wip_entity_id = wro.wip_entity_id
     AND we.wip_entity_name = NVL (:p_job, we.wip_entity_name)
     AND ood.organization_id = NVL (:orga, ood.organization_id)
ORDER BY wro.operation_seq_num

GL_ACCESS_SET_LEDGERS

SELECT  glasna.access_set_id    AS ACCESS_SET_ID,
               DECODE(gllsa.ledger_id,
                      NULL, glasna.ledger_id,
                      gllsa.ledger_id) AS LEDGER_ID,
               DECODE(MIN(DECODE(glasna.all_segment_value_flag,
                                 'Y',
                                 DECODE(glasna.access_privilege_code,
                                        'B', 1, 'R', 3),
                                 DECODE(glasna.access_privilege_code,
                                        'B', 2, 'R', 3))),                    
                       1, 'F',
                       2, 'B',
                       3, 'R')  AS ACCESS_PRIVILEGE_CODE,
               MAX(glasna.LAST_UPDATE_DATE) AS LAST_UPDATE_DATE,
               0 AS LAST_UPDATED_BY,
               MAX(glasna.CREATION_DATE) AS CREATION_DATE,
               0 AS CREATED_BY,
               0 AS LAST_UPDATE_LOGIN,
               TO_DATE(NULL)                   AS START_DATE,
               TO_DATE(NULL)                   AS END_DATE
       FROM    GL_ACCESS_SETS glas,
               GL_ACCESS_SET_NORM_ASSIGN glasna,
               GL_LEDGER_SET_ASSIGNMENTS gllsa                                
       WHERE   glas.automatically_created_flag = 'N'
       AND     glasna.access_set_id = glas.access_set_id
       AND     NVL(glasna.status_code, 'X') <> 'I'
       AND     gllsa.ledger_set_id (+) = glasna.ledger_id
       GROUP BY glasna.access_set_id,
                DECODE(gllsa.ledger_id, NULL,
                       glasna.ledger_id, gllsa.ledger_id)
       UNION ALL
       Select  glas.access_set_id AS ACCESS_SET_ID,
       DECODE(gll.object_type_code,'S',gllsa.ledger_id,glasna.ledger_id) AS LEDGER_ID,
       DECODE(MAX(DECODE(gll.object_type_code,'S',1,DECODE(gllsa.ledger_id,NULL,
                                                      DECODE(glasna.all_segment_value_flag,'Y',
                                                             DECODE(glasna.access_privilege_code,'R',3,'B',1),
                                                             DECODE(glasna.access_privilege_code,'R',3,'B',2)),
                                                      DECODE(glasna2.all_segment_value_flag,'Y',
                                                             DECODE(glasna2.access_privilege_code,'R',3,'B',1),
                                                             DECODE(glasna2.access_privilege_code,'R',3,'B',2))))),
                                         1,'F',2,'B',3,'R') AS ACCESS_PRIVILEGE_CODE,
   MAX(glasna2.LAST_UPDATE_DATE) AS LAST_UPDATE_DATE,
   0 AS LAST_UPDATED_BY,
   MAX(glasna2.CREATION_DATE) AS CREATION_DATE,
   0 AS CREATED_BY,
   0 AS LAST_UPDATE_LOGIN,
   TO_DATE(NULL) AS START_DATE,
   TO_DATE(NULL) AS END_DATE  
FROM   gl_ledgers gll,
       gl_access_sets glas,
       gl_access_set_norm_assign glasna,
       gl_ledger_set_assignments gllsa,
       gl_access_set_norm_assign glasna2
WHERE  glas.access_set_id = gll.implicit_access_set_id
AND    glas.automatically_created_flag = 'Y'
AND    glasna.access_set_id = glas.access_set_id
AND    NVL(glasna.status_code,'X') <> 'I'
AND    gllsa.ledger_set_id (+) = glasna.ledger_id
AND    NVL(gllsa.status_code (+),'X') <> 'I'
AND    glasna2.access_set_id = DECODE(gllsa.ledger_set_id , NULL, glasna.access_set_id, glasna.access_set_id)
AND    glasna2.ledger_id = DECODE(gll.object_type_code,'S',glasna.ledger_id,NVL(gllsa.ledger_id,glasna.ledger_id))
AND    NVL(glasna2.status_code, 'X') <> 'I'
GROUP BY   glas.access_set_id,
           DECODE(gll.object_type_code,'S',gllsa.ledger_id,glasna.ledger_id)