Loganda's Blog

Wednesday, June 25, 2014

Replace Function in Oracle SQL

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

Tuesday, June 24, 2014

Legal Entity Oracle Apps

Sunday, April 27, 2014

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

Thursday, February 27, 2014

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));

Wednesday, February 19, 2014

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
);
**************/

Tuesday, February 4, 2014

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