Substr and instr example

 Select Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',': ',1,2)+2 ace, Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',' ',-1,1) ace1,

    Substr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',': ',1,2)+2,

  Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',' ',-1,1)-(Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',': ',1,2)+2)) exact_value 

 From Dual;

Inventory Item Creation OPI

ITEM IMPORT - INCOIN

Pre-requisites:

* Creating an Organization
* Code Combinations
* Templates
* Defining Item Status Codes
* Defining Item Types

Interface tables:
1. MTL_SYSTEM_ITEMS_INTERFACE
2. MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
3. MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
4. MTL_INTERFACE_ERRORS (View Fallouts after import)

Concurrent Programs:
1. Import Items

Parameters :

Process Set (Null for All) - Set_Process_Id = Some Number - Non-Required numeric value set
Create or Update Items = CREATE New Items or UPDATE Existing Items
Validate Items = Yes or No
Process Items = Yes or No
Delete Processed Rows = Yes or No
Gather Statistics = Yes or No

Base Table:

1. MTL_SYSTEM_ITEMS_B
2. MTL_ITEM_REVISIONS_B
3. MTL_CATEGORIES_B
4. MTL_CATEGORY_SETS_B
5. MTL_ITEM_STATUS
6. MTL_ITEM_TEMPLATES

Validation:
1. Check for valid item type. (FND_LOOKUP_TYPES_VL where LOOKUP_TYPE = 'ITEM_TYPE')
2. Check for valid part_id/segment of the source table. (MTL_SYSTEM_ITEMS_B)
3. Validate part_id/segment1 for master org.  (MTL_SYSTEM_ITEMS_B, MTL_PARAMETERS)
4. Validate and translate template id of the source table. (MTL_ITEM_TEMPLATES)
5. Check for valid template id. (Attributes are already set for items, default attributes for that template, i.e., purchasable, stockable, etc )
6. Check for valid item status.
7. Validate primary uom of the source table.
8. Validate attribute values.
9. Validate other UOMs of the source table.
10. Check for unique item type. Discard the item, if part has non-unique item type.
11. Check for description, inv_um uniqueness
12. Validate Inventory Organization id.
13. Load master records and category records only if all Load child record if no error found.


Some important columns that need to populated in the MTL_SYSTEM_ITEMS_INTERFACE tables
1. PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete, 3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded)
2. TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
3. SET_PROCESS_ID = 1
4. ORGANIZATION_ID
5. DESCRIPTION
6. ITEM_NUMBER and/or SEGMENT (n)
7. MATERIAL_COST
8. REVISION
9. TEMPLATE_ID
10. SUMMARY_FLAG
11. ENABLED_FLAG
12. PURCHASING_ITEM_FLAG
13. SALES_ACCOUNT (defaulted from MTL_PARAMETERS.SALES_ACCOUNT)
14. COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)

Links for AP - SLA / XLA - GL

Select Distinct Xal.Accounting_Date Gl_Date,Xah.Je_Category_Name Tr_Type, Ap.Segment1 Vendor_Num, Ap.Vendor_Name,Pvs.Vendor_Site_Code As Vendor_Site_Name ,
Aia.Doc_Sequence_Value Voucher_No, Aia.Invoice_Num, Aia.Invoice_Date, Xal.Currency_Code, Xal.Entered_Dr,Xal.Entered_Cr,Xal.Accounted_Dr,Xal.Accounted_Cr ,
Replace(Xal.Description, Chr(10),' ') Remarks
 From
Apps.Gl_Import_References Gir,
Apps.Gl_Je_Headers Gh,
Apps.Gl_Je_Lines Gl,
Apps.Xla_Ae_Lines Xal ,
Apps.Xla_Ae_Headers Xah,
Apps.Xla_Transaction_Entities_Upg Xte,
Apps.Ap_Invoices_All Aia,
Apps.Ap_Suppliers Ap,
Apps.Po_Vendor_Sites_All Pvs,
Apps.Gl_Code_Combinations Gcc
Where
Xal.Gl_Sl_Link_Id=Gir.Gl_Sl_Link_Id
And Xal.Ae_Header_Id = Xah.Ae_Header_Id
And Gir.Je_Header_Id = Gl.Je_Header_Id
And Gir.Je_Line_Num = Gl.Je_Line_Num
And Gl.Je_Header_Id = Gh.Je_Header_Id
And Xah.Entity_Id = Xte.Entity_Id
And Xte.Source_Id_Int_1 = Aia.Invoice_Id
And Xte.Security_Id_Int_1 = Aia.Org_Id
And Aia.Vendor_Id = Ap.Vendor_Id
And Ap.Vendor_Id = Pvs.Vendor_Id
And Aia.Vendor_Site_Id = Pvs.Vendor_Site_Id
And Gcc.Code_Combination_Id = Xal.Code_Combination_Id
And Gcc.Code_Combination_Id = Gl.Code_Combination_Id
And Aia.Org_Id = Nvl(:Org, Aia.Org_Id)
and xal.source_table in ('AP_INVOICES','AP_CHECKS')
And Nvl(Xah.Gl_Transfer_Status_Code,'N') = 'Y';

Download And Upload Report using Command Prompt FNDLOAD

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct /exceltest/TEST_UAT/UAT_TEST/apps/apps_st/appl/xxeil/12.0.0/reports/US/XX_WIP.ldt PROGRAM APPLICATION_SHORT_NAME="XXEIL" CONCURRENT_PROGRAM_NAME="XX_WIP"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_WIP.ldt

Order To Cash Cycle

Order To Cash Process Steps:
1. Enter the Sales Order
2. Book the Sales Order
3. Launch the Pick List
4. Ship Confirm
5. Creative Invoice
6. Create the Receipts either manually or using Auto Lockbox
7. Transfer to GL
8. Journal import
9. Posting

Common Profiles to be done:
Before start to configure the setups needs to set the following profiles at Responsibility Level,
1. Qp : Item Validation Organization – IMO to be attached
2. Qp : Pricing Transaction Entity – Order Fulfilment
3. Qp: Source System Code – Oracle Pricing – (it is for Order Management)

Prerequisites:
1. Customer Profile Class
2. System Options
3. System Parameters
4. Pick Slip Grouping Rule
5. Release Sequencing Rule
6. Release Rule
7. Transaction Type
8. Document Sequence
9. Document Sequence Assignment
10. Grants & Roles
11. Customers
12. Profile Options

Basic Setups:
Go to Order Management Responsibility:
1. Customer Profile Option
Navigation Path – Setup  Customers  Profile Classes
2. System Options
Navigation Path - Setup  Customers  System Options
3. System Parameters
Navigation Path - Setup  System Parameters  Values
Note:
Once System Options and System Parameters defined, users are able to view the Sales Order form. Otherwise it will be freeze out field.
4.  Pick Slip Grouping Rule
Navigation Path - Setup  Shipping  Picking  Define Pick Slip Grouping Rules

5. Define Release Sequence Rule
Navigation Path - Setup  Shipping  Picking  Releasing Rule
6. Define Release Rules
Navigation Path - Setup  Shipping  Picking  Define Releasing Rule
7. Roles Definitions
Navigation Path - Setup  Shipping  Grants and Roles Definitions  Define Roles
8. Grants Definitions
Navigation Path - Setup  Shipping  Grants and Roles Definitions  Grants
9. Transaction Type
Navigation Path - Setup  Transaction Types  Define
10. Transaction Type Approvals
Navigation Path - Setup  Transaction Types  Approval

11. Document Sequence
Navigation Path - Setup  Shipping  Documents  Document Sequences
12. Document Category creations
Navigation Path - Setup  Shipping  Documents  Document Categories
13. Document Sequence Assignments
Navigation Path - Setup  Shipping  Documents  Document Sequences Assignment
14. Sales Person Creation
Navigation Path – Setup  Sales Persons
15. Price list Creation
Navigation Path – Pricing  Price Lists  Price List Setup
16. Payment Terms
Navigation Path – Setup  Orders  Payment Terms

17. Customer Creation
Navigation Path – Customers  Standard

Note: If The Data that defines inconsistent error shows when navigate to Account site from Customer Account –
Root Cause - To solve the problem, go to Flex Field and take Territory Flex field and Application is ‘Receivables’ is not frozen.

Solution – Freeze Territory Key Flex Field (Application – Receivables)

Major setups to be create in Customers:
1. Bill to site to be created
2. Ship to site to be created
3. Primary Bill to and Primary Ship to site enabled
4. In order Management Tab – select the inventory org in warehouse field
5. Select the Order type, Price List, Warehouse to be assigned in Account Site form

WRITE TO A FILE USING UTL_FILE

• Create a Table

 Create Table xx_parameters (

 param_name VARCHAR2(100) ,

 param_value NUMBER );

• Input some parameters
• Create a Directory 

CREATE OR REPLACE DIRECTORY DIR_MYTEMP AS '/home/oracle/ebsutl12/';

• Create the Procedure

CREATE OR REPLACE PROCEDURE xxtest_procedure_utl

AS

   CURSOR c_cur1

   IS

      SELECT param_name, param_value

        FROM xx_parameters;

   l_file   UTL_FILE.file_type;

   l_dir    VARCHAR2 (50)      := 'DIR_MYTEMP';

   l_name   VARCHAR2 (20)      := 'mydatafile.txt';

BEGIN

   l_file := UTL_FILE.fopen (l_dir, l_name, 'W');

   FOR cur_rec IN c_cur1

   LOOP

      UTL_FILE.put_line (l_file,

                         cur_rec.param_name || ',' || cur_rec.param_value

                        );

   END LOOP;

   UTL_FILE.fclose (l_file);

EXCEPTION

   WHEN UTL_FILE.invalid_path

   THEN

      DBMS_OUTPUT.put_line (‘invalid_path’);

      RAISE;

   WHEN UTL_FILE.invalid_mode

   THEN

      DBMS_OUTPUT.put_line (‘invalid_mode’);

      RAISE;

   WHEN UTL_FILE.invalid_filehandle

   THEN

      DBMS_OUTPUT.put_line (‘invalid_filehandle’);

      RAISE;

   WHEN UTL_FILE.invalid_operation

   THEN

      DBMS_OUTPUT.put_line (‘invalid_operation’);

      RAISE;

   WHEN UTL_FILE.read_error

   THEN

      DBMS_OUTPUT.put_line (‘read_error’);

      RAISE;

   WHEN UTL_FILE.write_error

   THEN

      DBMS_OUTPUT.put_line (‘write_error’);

      RAISE;

   WHEN UTL_FILE.internal_error

   THEN

      DBMS_OUTPUT.put_line (‘internal_error’);

      RAISE;

END xxtest_procedure_utl;

/

• Executing the procedure will create a file named mydatafile.txt in directory /home/oracle/ebsutl12/

Sample of DBMS_LOCK.SLEEP

• Create a Table

Create Table T_Sleep_Test(
S_No Number,
Dateandtime Date);

• Create a Procedure

CREATE OR REPLACE PROCEDURE XXTEST_PROCEDURE

IS

   l_cnt   NUMBER;

BEGIN

   l_cnt := 0;

   EXECUTE IMMEDIATE 'Truncate Table Xxsleep_table';

   FOR l_cnt IN 1 .. 5

   LOOP

      INSERT INTO xxsleep_table

           VALUES (l_cnt, SYSDATE);

      COMMIT;

      DBMS_LOCK.sleep (10);

   END LOOP;

END;

• Values get inserted into the table created above in an interval of 10 seconds

Exec XXTEST_PROCEDURE;

• Check once the execution of the procedure gets completed.

Select S_No, To_Char(Dateandtime,'DD-MON-RRRR HH24:MI:SS') Ace From   Xxsleep_table;

Locked Session in Oracle

Select L.Session_Id||','||V.Serial# Sid_Serial,
       L.Oracle_Username Ora_User,
       O.Object_Name,
       O.Object_Type,
       Decode(L.Locked_Mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive',
          To_Char(L.Locked_Mode)
       ) Lock_Mode,
       O.Status,
       To_Char(O.Last_Ddl_Time,'dd.mm.yy') Last_DDL
From Dba_Objects O, Gv$Locked_Object L, V$Session V
Where O.Object_Id = L.Object_Id
      And L.Session_Id=V.Sid
Order By 2,3;

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

ALTER SYSTEM KILL SESSION  'SID,SERIAL'; ( '633,857' );

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