[Home] [Help]
12: ---------------------- PRIVATE PROCEDURES PROTOTYPE ----------------------
13: --------------------------------------------------------------------------
14: PROCEDURE derive_ship_to_ou_id
15: (
16: p_key IN po_session_gt.key%TYPE,
17: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
18: p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
19: x_ship_to_ou_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
20: );
20: );
21:
22: PROCEDURE derive_deliver_to_loc_id
23: (
24: p_key IN po_session_gt.key%TYPE,
25: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
26: p_deliver_to_loc_tbl IN PO_TBL_VARCHAR100,
27: x_deliver_to_loc_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
28: );
28: );
29:
30: PROCEDURE derive_deliver_to_person_id
31: (
32: p_key IN po_session_gt.key%TYPE,
33: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
34: p_person_name_tbl IN PO_TBL_VARCHAR2000,
35: x_person_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
36: );
36: );
37:
38: PROCEDURE derive_dest_type_code
39: (
40: p_key IN po_session_gt.key%TYPE,
41: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
42: p_dest_type_tbl IN PO_TBL_VARCHAR30,
43: x_dest_type_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
44: );
44: );
45:
46: PROCEDURE derive_dest_org_id
47: (
48: p_key IN po_session_gt.key%TYPE,
49: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
50: p_dest_org_tbl IN PO_TBL_VARCHAR100,
51: p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
52: x_dest_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
53: );
54:
55: PROCEDURE derive_wip_entity_id
56: (
57: p_key IN po_session_gt.key%TYPE,
58: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
59: p_wip_entity_tbl IN PO_TBL_VARCHAR2000,
60: p_dest_org_id_tbl IN PO_TBL_NUMBER,
61: p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
63: );
64:
65: PROCEDURE derive_wip_line_id
66: (
67: p_key IN po_session_gt.key%TYPE,
68: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
69: p_wip_line_code_tbl IN PO_TBL_VARCHAR30,
70: p_dest_org_id_tbl IN PO_TBL_NUMBER,
71: p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
73: );
74:
75: PROCEDURE derive_ship_to_ou_coa_id
76: (
77: p_key IN po_session_gt.key%TYPE,
78: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
79: p_dest_org_id_tbl IN PO_TBL_NUMBER,
80: p_txn_flow_header_id_tbl IN PO_TBL_NUMBER,
81: p_dest_charge_account_id_tbl IN PO_TBL_NUMBER,
83: );
84:
85: PROCEDURE derive_bom_resource_id
86: (
87: p_key IN po_session_gt.key%TYPE,
88: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
89: p_bom_resource_code_tbl IN PO_TBL_VARCHAR30,
90: p_dest_org_id_tbl IN PO_TBL_NUMBER,
91: x_bom_resource_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
98: );
99:
100: PROCEDURE derive_project_info
101: (
102: p_key IN po_session_gt.key%TYPE,
103: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
104: p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
105: x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
106: );
106: );
107:
108: PROCEDURE derive_project_id
109: (
110: p_key IN po_session_gt.key%TYPE,
111: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
112: p_project_tbl IN PO_TBL_VARCHAR30,
113: p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
114: p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
118: );
119:
120: PROCEDURE derive_task_id
121: (
122: p_key IN po_session_gt.key%TYPE,
123: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
124: p_task_tbl IN PO_TBL_VARCHAR30,
125: p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
126: p_project_id_tbl IN PO_TBL_NUMBER,
130: );
131:
132: PROCEDURE derive_expenditure_type
133: (
134: p_key IN po_session_gt.key%TYPE,
135: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
136: p_expenditure_tbl IN PO_TBL_VARCHAR100,
137: p_project_id_tbl IN PO_TBL_NUMBER,
138: p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
140: );
141:
142: PROCEDURE derive_expenditure_org_id
143: (
144: p_key IN po_session_gt.key%TYPE,
145: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
146: p_expenditure_org_tbl IN PO_TBL_VARCHAR100,
147: p_project_id_tbl IN PO_TBL_NUMBER,
148: p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
157:
158:
159: PROCEDURE get_item_status
160: (
161: p_key IN po_session_gt.key%TYPE,
162: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
163: p_item_id_tbl IN PO_TBL_NUMBER,
164: p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
165: x_item_status_tbl OUT NOCOPY PO_TBL_VARCHAR1
727: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
728: d_position NUMBER;
729:
730: -- key value used to identify rows in temp table
731: l_key po_session_gt.key%TYPE;
732:
733: -- table to hold index
734: l_index_tbl DBMS_SQL.NUMBER_TABLE;
735:
1496: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1497: d_position NUMBER;
1498:
1499: -- key value used to identify row in temp table
1500: l_key po_session_gt.key%TYPE;
1501:
1502: -- table to hold index
1503: l_index_tbl DBMS_SQL.NUMBER_TABLE;
1504:
2274: --End of Comments
2275: ------------------------------------------------------------------------
2276: PROCEDURE derive_ship_to_ou_id
2277: (
2278: p_key IN po_session_gt.key%TYPE,
2279: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2280: p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
2281: x_ship_to_ou_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2282: ) IS
2300: x_ship_to_ou_id_tbl.EXTEND(p_index_tbl.COUNT);
2301:
2302: -- query database to get derived result in batch mode
2303: FORALL i IN 1..p_index_tbl.COUNT
2304: INSERT INTO po_session_gt(key, num1, num2)
2305: SELECT p_key,
2306: p_index_tbl(i),
2307: TO_NUMBER(org_info.org_information3)
2308: FROM hr_organization_information org_info,
2314: d_position := 10;
2315:
2316: -- retrieve values from temp table and delete the records at the
2317: -- same time
2318: DELETE FROM po_session_gt
2319: WHERE key = p_key
2320: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2321:
2322: d_position := 20;
2369: --End of Comments
2370: ------------------------------------------------------------------------
2371: PROCEDURE derive_deliver_to_loc_id
2372: (
2373: p_key IN po_session_gt.key%TYPE,
2374: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2375: p_deliver_to_loc_tbl IN PO_TBL_VARCHAR100,
2376: x_deliver_to_loc_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2377: ) IS
2394: END IF;
2395:
2396: -- query database to get derived result in batch mode
2397: FORALL i IN 1..p_index_tbl.COUNT
2398: INSERT INTO po_session_gt(key, num1, num2)
2399: SELECT p_key,
2400: p_index_tbl(i),
2401: location_id
2402: FROM hr_locations
2407: d_position := 10;
2408:
2409: -- retrieve values from temp table and delete the records at the
2410: -- same time
2411: DELETE FROM po_session_gt
2412: WHERE key = p_key
2413: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2414:
2415: d_position := 20;
2462: --End of Comments
2463: ------------------------------------------------------------------------
2464: PROCEDURE derive_deliver_to_person_id
2465: (
2466: p_key IN po_session_gt.key%TYPE,
2467: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2468: p_person_name_tbl IN PO_TBL_VARCHAR2000,
2469: x_person_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2470: ) IS
2485: END IF;
2486:
2487: -- query database to get derived result in batch mode
2488: FORALL i IN 1..p_index_tbl.COUNT
2489: INSERT INTO po_session_gt(key, num1, num2)
2490: SELECT p_key,
2491: p_index_tbl(i),
2492: employee_id
2493: FROM hr_employees_all_v
2498: d_position := 10;
2499:
2500: -- retrieve values from temp table and delete the records at the
2501: -- same time
2502: DELETE FROM po_session_gt
2503: WHERE key = p_key
2504: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2505:
2506: d_position := 20;
2553: --End of Comments
2554: ------------------------------------------------------------------------
2555: PROCEDURE derive_dest_type_code
2556: (
2557: p_key IN po_session_gt.key%TYPE,
2558: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2559: p_dest_type_tbl IN PO_TBL_VARCHAR30,
2560: x_dest_type_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
2561: ) IS
2576: END IF;
2577:
2578: -- query database to get derived result in batch mode
2579: FORALL i IN 1..p_index_tbl.COUNT
2580: INSERT INTO po_session_gt(key, num1, char1)
2581: SELECT p_key,
2582: p_index_tbl(i),
2583: lookup_code
2584: FROM po_destination_types_all_v
2589: d_position := 10;
2590:
2591: -- retrieve values from temp table and delete the records at the
2592: -- same time
2593: DELETE FROM po_session_gt
2594: WHERE key = p_key
2595: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2596:
2597: d_position := 20;
2646: --End of Comments
2647: ------------------------------------------------------------------------
2648: PROCEDURE derive_dest_org_id
2649: (
2650: p_key IN po_session_gt.key%TYPE,
2651: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2652: p_dest_org_tbl IN PO_TBL_VARCHAR100,
2653: p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
2654: x_dest_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2671: END IF;
2672:
2673: -- query database to get derived result in batch mode
2674: FORALL i IN 1..p_index_tbl.COUNT
2675: INSERT INTO po_session_gt(key, num1, num2)
2676: SELECT p_key,
2677: p_index_tbl(i),
2678: organization_id
2679: FROM org_organization_definitions
2687:
2688: -- set destination_organization_id as ship_to_organization
2689: -- if destination_organization is null
2690: FORALL i IN 1..p_index_tbl.COUNT
2691: INSERT INTO po_session_gt(key, num1, num2)
2692: SELECT p_key,
2693: p_index_tbl(i),
2694: p_ship_to_org_id_tbl(i)
2695: FROM dual
2699: d_position := 20;
2700:
2701: -- retrieve values from temp table and delete the records at the
2702: -- same time
2703: DELETE FROM po_session_gt
2704: WHERE key = p_key
2705: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2706:
2707: d_position := 30;
2758: --End of Comments
2759: ------------------------------------------------------------------------
2760: PROCEDURE derive_wip_entity_id
2761: (
2762: p_key IN po_session_gt.key%TYPE,
2763: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2764: p_wip_entity_tbl IN PO_TBL_VARCHAR2000,
2765: p_dest_org_id_tbl IN PO_TBL_NUMBER,
2766: p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
2785: END IF;
2786:
2787: -- query database to get derived result in batch mode
2788: FORALL i IN 1..p_index_tbl.COUNT
2789: INSERT INTO po_session_gt(key, num1, num2)
2790: SELECT p_key,
2791: p_index_tbl(i),
2792: wip_entity_id
2793: FROM wip_entities
2800: d_position := 10;
2801:
2802: -- retrieve values from temp table and delete the records at the
2803: -- same time
2804: DELETE FROM po_session_gt
2805: WHERE key = p_key
2806: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2807:
2808: d_position := 20;
2859: --End of Comments
2860: ------------------------------------------------------------------------
2861: PROCEDURE derive_wip_line_id
2862: (
2863: p_key IN po_session_gt.key%TYPE,
2864: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2865: p_wip_line_code_tbl IN PO_TBL_VARCHAR30,
2866: p_dest_org_id_tbl IN PO_TBL_NUMBER,
2867: p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
2886: END IF;
2887:
2888: -- query database to get derived result in batch mode
2889: FORALL i IN 1..p_index_tbl.COUNT
2890: INSERT INTO po_session_gt(key, num1, num2)
2891: SELECT p_key,
2892: p_index_tbl(i),
2893: line_id
2894: FROM wip_lines
2901: d_position := 10;
2902:
2903: -- retrieve values from temp table and delete the records at the
2904: -- same time
2905: DELETE FROM po_session_gt
2906: WHERE key = p_key
2907: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2908:
2909: d_position := 20;
2959: --End of Comments
2960: ------------------------------------------------------------------------
2961: PROCEDURE derive_ship_to_ou_coa_id
2962: (
2963: p_key IN po_session_gt.key%TYPE,
2964: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2965: p_dest_org_id_tbl IN PO_TBL_NUMBER,
2966: p_txn_flow_header_id_tbl IN PO_TBL_NUMBER,
2967: p_dest_charge_account_id_tbl IN PO_TBL_NUMBER,
2989: END IF;
2990:
2991: -- query database to get derived result in batch mode
2992: FORALL i IN 1..p_index_tbl.COUNT
2993: INSERT INTO po_session_gt(key, num1, num2)
2994: SELECT p_key,
2995: p_index_tbl(i),
2996: sob.chart_of_accounts_id
2997: FROM gl_sets_of_books sob,
3007: d_position := 10;
3008:
3009: -- retrieve values from temp table and delete the records at the
3010: -- same time
3011: DELETE FROM po_session_gt
3012: WHERE key = p_key
3013: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3014:
3015: d_position := 20;
3064: --End of Comments
3065: ------------------------------------------------------------------------
3066: PROCEDURE derive_bom_resource_id
3067: (
3068: p_key IN po_session_gt.key%TYPE,
3069: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3070: p_bom_resource_code_tbl IN PO_TBL_VARCHAR30,
3071: p_dest_org_id_tbl IN PO_TBL_NUMBER,
3072: x_bom_resource_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
3091: END IF;
3092:
3093: -- query database to get derived result in batch mode
3094: FORALL i IN 1..p_index_tbl.COUNT
3095: INSERT INTO po_session_gt(key, num1, num2)
3096: SELECT p_key,
3097: p_index_tbl(i),
3098: resource_id
3099: FROM bom_resources
3105: d_position := 10;
3106:
3107: -- retrieve values from temp table and delete the records at the
3108: -- same time
3109: DELETE FROM po_session_gt
3110: WHERE key = p_key
3111: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3112:
3113: d_position := 20;
3499: --End of Comments
3500: ------------------------------------------------------------------------
3501: PROCEDURE derive_project_info
3502: (
3503: p_key IN po_session_gt.key%TYPE,
3504: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3505: p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
3506: x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
3507: ) IS
3624: --End of Comments
3625: ------------------------------------------------------------------------
3626: PROCEDURE derive_project_id
3627: (
3628: p_key IN po_session_gt.key%TYPE,
3629: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3630: p_project_tbl IN PO_TBL_VARCHAR30,
3631: p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
3632: p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
3655:
3656: -- query database to get derived result in batch mode
3657: -- query different views based on value of destination_type_code
3658: FORALL i IN INDICES OF p_derive_row_tbl
3659: INSERT INTO po_session_gt(key, num1, num2)
3660: SELECT p_key,
3661: p_index_tbl(i),
3662: project_id
3663: FROM pa_projects_expend_v
3668:
3669: d_position := 10;
3670:
3671: FORALL i IN INDICES OF p_derive_row_tbl
3672: INSERT INTO po_session_gt(key, num1, num2)
3673: SELECT p_key,
3674: p_index_tbl(i),
3675: project_id
3676: FROM pjm_projects_org_ou_v
3684: d_position := 20;
3685:
3686: -- retrieve values from temp table and delete the records at the
3687: -- same time
3688: DELETE FROM po_session_gt
3689: WHERE key = p_key
3690: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3691:
3692: d_position := 30;
3747: --End of Comments
3748: ------------------------------------------------------------------------
3749: PROCEDURE derive_task_id
3750: (
3751: p_key IN po_session_gt.key%TYPE,
3752: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3753: p_task_tbl IN PO_TBL_VARCHAR30,
3754: p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
3755: p_project_id_tbl IN PO_TBL_NUMBER,
3778:
3779: -- query database to get derived result in batch mode
3780: -- query different views based on value of destination_type_code
3781: FORALL i IN INDICES OF p_derive_row_tbl
3782: INSERT INTO po_session_gt(key, num1, num2)
3783: SELECT p_key,
3784: p_index_tbl(i),
3785: task_id
3786: FROM pa_tasks_expend_v
3792:
3793: d_position := 10;
3794:
3795: FORALL i IN INDICES OF p_derive_row_tbl
3796: INSERT INTO po_session_gt(key, num1, num2)
3797: SELECT p_key,
3798: p_index_tbl(i),
3799: task_id
3800: FROM pa_tasks_all_expend_v
3808: d_position := 20;
3809:
3810: -- retrieve values from temp table and delete the records at the
3811: -- same time
3812: DELETE FROM po_session_gt
3813: WHERE key = p_key
3814: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3815:
3816: d_position := 30;
3867: --End of Comments
3868: ------------------------------------------------------------------------
3869: PROCEDURE derive_expenditure_type
3870: (
3871: p_key IN po_session_gt.key%TYPE,
3872: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3873: p_expenditure_tbl IN PO_TBL_VARCHAR100,
3874: p_project_id_tbl IN PO_TBL_NUMBER,
3875: p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
3893: END IF;
3894:
3895: -- query database to get derived result in batch mode
3896: FORALL i IN INDICES OF p_derive_row_tbl
3897: INSERT INTO po_session_gt(key, num1, char1)
3898: SELECT p_key,
3899: p_index_tbl(i),
3900: expenditure_type
3901: FROM pa_expenditure_types_v
3907: d_position := 10;
3908:
3909: -- retrieve values from temp table and delete the records at the
3910: -- same time
3911: DELETE FROM po_session_gt
3912: WHERE key = p_key
3913: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3914:
3915: d_position := 20;
3967: --End of Comments
3968: ------------------------------------------------------------------------
3969: PROCEDURE derive_expenditure_org_id
3970: (
3971: p_key IN po_session_gt.key%TYPE,
3972: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3973: p_expenditure_org_tbl IN PO_TBL_VARCHAR100,
3974: p_project_id_tbl IN PO_TBL_NUMBER,
3975: p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
3994: END IF;
3995:
3996: -- query database to get derived result in batch mode
3997: FORALL i IN INDICES OF p_derive_row_tbl
3998: INSERT INTO po_session_gt(key, num1, num2)
3999: SELECT p_key,
4000: p_index_tbl(i),
4001: organization_id
4002: FROM pa_organizations_expend_v
4009: d_position := 10;
4010:
4011: -- retrieve values from temp table and delete the records at the
4012: -- same time
4013: DELETE FROM po_session_gt
4014: WHERE key = p_key
4015: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4016:
4017: d_position := 20;
4064: --End of Comments
4065: ------------------------------------------------------------------------
4066: PROCEDURE get_item_status
4067: (
4068: p_key IN po_session_gt.key%TYPE,
4069: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4070: p_item_id_tbl IN PO_TBL_NUMBER,
4071: p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
4072: x_item_status_tbl OUT NOCOPY PO_TBL_VARCHAR1
4091: x_item_status_tbl := PO_TBL_VARCHAR1();
4092: x_item_status_tbl.EXTEND(p_index_tbl.COUNT);
4093:
4094: FORALL i IN 1..p_index_tbl.COUNT
4095: INSERT INTO po_session_gt(key, num1, char1)
4096: SELECT p_key,
4097: p_index_tbl(i),
4098: DECODE(outside_operation_flag,'Y','O',
4099: DECODE(stock_enabled_flag,'Y','E','D'))
4104: d_position := 10;
4105:
4106: -- retrieve values from temp table and delete the records at the
4107: -- same time
4108: DELETE FROM po_session_gt
4109: WHERE key = p_key
4110: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4111:
4112: d_position := 20;