[Home] [Help]
PACKAGE BODY: APPS.GML_PO_HEADER_ARCHIVE
Source
1 PACKAGE BODY GML_PO_HEADER_ARCHIVE AS
2 /* $Header: GMLPOHAB.pls 120.1 2005/09/30 13:41:34 pbamb noship $ */
3
4 v_segment1 VARCHAR2(32);
5 v_revision_num NUMBER;
6 v_agent_id PO_HEADERS_ALL.AGENT_ID%TYPE;
7 v_bill_to_location_id PO_HEADERS_ALL.BILL_TO_LOCATION_ID%TYPE;
8 v_terms_id PO_HEADERS_ALL.TERMS_ID%TYPE;
9 v_freight_code PO_HEADERS_ALL.FREIGHT_TERMS_LOOKUP_CODE%TYPE;
10 v_fob_code PO_HEADERS_ALL.FOB_LOOKUP_CODE%TYPE;
11 v_carrier_code PO_HEADERS_ALL.SHIP_VIA_LOOKUP_CODE%TYPE;
12 v_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
13 err_num NUMBER;
14 err_msg VARCHAR2(100);
15
16 /*========================================================================+
17 | PROCEDURE store |
18 | |
19 | DESCRIPTION Stores po_no, revision_num, agent_id and bill_to_location |
20 | |
21 | MODIFICATION HISTORY |
22 | 6-DEC-97 Kenny --- Created. |
23 | 13-MAY-99 NC --- Added terms_id,freight_code.fob_code and |
24 | carrier_code (Bug #788658). |
25 | |
26 | 03-27-00 HW BUG#:1222249 store po_header_id |
27 +========================================================================*/
28
29 PROCEDURE store(p_segment1 IN VARCHAR2, p_revision_num IN NUMBER,
30 p_agent_id IN PO_HEADERS_ALL.AGENT_ID%TYPE,
31 p_bill_to_location_id IN PO_HEADERS_ALL.BILL_TO_LOCATION_ID%TYPE,
32 p_terms_id IN PO_HEADERS_ALL.TERMS_ID%TYPE,
33 p_freight_code IN PO_HEADERS_ALL.FREIGHT_TERMS_LOOKUP_CODE%TYPE,
34 p_fob_code IN PO_HEADERS_ALL.FOB_LOOKUP_CODE%TYPE,
35 p_carrier_code IN PO_HEADERS_ALL.SHIP_VIA_LOOKUP_CODE%TYPE,
36 p_po_header_id IN PO_LINE_LOCATIONS_ARCHIVE_ALL.PO_HEADER_ID%TYPE)
37 AS
38 BEGIN
39 v_segment1 := p_segment1;
40 v_revision_num := p_revision_num;
41 v_agent_id := p_agent_id;
42 v_bill_to_location_id := p_bill_to_location_id;
43 v_terms_id := p_terms_id;
44 v_freight_code := p_freight_code;
45 v_fob_code := p_fob_code;
46 v_carrier_code := p_carrier_code;
47 v_po_header_id := p_po_header_id ;
48
49 END;
50
51
52 /*=======================================================================+
53 | PROCEDURE process |
54 | |
55 | DESCRIPTION This procedure checks if agent_id or bill to location id |
56 | is different from that of a previous revision. If so, |
57 | resubmit the PO by calling the resubmission procedure. |
58 | |
59 | MODIFICATION HISTORY |
60 | 5-DEC-97 Kenny --- Created. |
61 | 13-MAY-99 NC --- Modified to call resub() when terms_id,fob_code,|
62 | carrier_code and freight_code are changed. |
63 | (Bug #788658). |
64 | |
65 | 03-27-00 HW BUG#:1222249 - Find the proper PO to synch |
66 | and fix problem with mutating by not retrieving |
67 | any info from po_headers_archive_all |
68 +========================================================================*/
69
70 PROCEDURE process
71 AS
72 v_old_agent_id PO_HEADERS_ALL.AGENT_ID%TYPE;
73 v_old_bill_to_location_id PO_HEADERS_ALL.BILL_TO_LOCATION_ID%TYPE;
74 v_old_terms_id PO_HEADERS_ALL.TERMS_ID%TYPE;
75 v_old_freight_code PO_HEADERS_ALL.FREIGHT_TERMS_LOOKUP_CODE%TYPE;
76 v_old_fob_code PO_HEADERS_ALL.FOB_LOOKUP_CODE%TYPE;
77 v_old_carrier_code PO_HEADERS_ALL.SHIP_VIA_LOOKUP_CODE%TYPE;
78 v_from_date VARCHAR2(13) := SYSDATE;
79 v_to_date VARCHAR2(13) := SYSDATE;
80 errbuf VARCHAR2(80);
81 retcode number;
82 v_po_line_id PO_LINE_LOCATIONS_ARCHIVE_ALL.PO_LINE_ID%TYPE;
83 v_ship_to_location_id PO_LINE_LOCATIONS_ARCHIVE_ALL.SHIP_TO_LOCATION_ID%TYPE;
84 v_line_location_id PO_LINE_LOCATIONS_ARCHIVE_ALL.LINE_LOCATION_ID%TYPE;
85
86 /* Cursor to get old agent id and bill to location id */
87 /* HW BUG#:1222249 */
88 /* This cursor causes a mutation problem */
89 /*
90
91 CURSOR po_headers_archive_cur(p_segment1 VARCHAR2,
92 p_revision_num NUMBER) IS
93 SELECT agent_id,
94 bill_to_location_id,
95 terms_id,
96 freight_terms_lookup_code,
97 fob_lookup_code,
98 ship_via_lookup_code
99
100 FROM po_headers_archive_all
101 WHERE segment1 = p_segment1
102 AND revision_num = p_revision_num;
103 */
104 /* BUG#:1132943 retrieve the correct line */
105
106 CURSOR line_loc_cur IS
107 SELECT po_line_id,ship_to_location_id,line_location_id
108 FROM po_line_locations_archive_all
109 WHERE po_header_id = v_po_header_id ;
110
111
112 BEGIN
113
114 IF v_revision_num > 0 THEN
115 /* BUG#:1222249 commented out the call to cursor */
116 /* and the resub routine. Just call GML_PO_INTERFACE.insert_rec */
117
118 /*
119 OPEN po_headers_archive_cur(v_segment1, v_revision_num-1);
120 FETCH po_headers_archive_cur
121 INTO v_old_agent_id,
122 v_old_bill_to_location_id,
123 v_old_terms_id,
124 v_old_freight_code,
125 v_old_fob_code,
126 v_old_carrier_code;
127
128 CLOSE po_headers_archive_cur;
129
130 IF (v_agent_id <> v_old_agent_id) OR
131 (v_bill_to_location_id <> v_old_bill_to_location_id) OR
132 (v_terms_id <> v_old_terms_id) OR
133 (v_freight_code <> v_old_freight_code) OR
134 (v_fob_code <> v_old_fob_code) OR
135 (v_carrier_code <> v_old_carrier_code) THEN
136
137 GML_PO_CON_REQ.po_resub(errbuf, retcode, v_from_date, v_to_date, v_segment1);
138 */
139
140 OPEN line_loc_cur ;
141 FETCH line_loc_cur INTO v_po_line_id,v_ship_to_location_id,v_line_location_id;
142 CLOSE line_loc_cur;
143 GML_PO_INTERFACE.insert_rec (v_po_header_id,
144 v_po_line_id,
145 v_line_location_id,
146 null,
147 null,
148 null,
149 null,
150 null,
151 null,
152 null,
153 null,
154 'N',
155 null,
156 v_ship_to_location_id, null);
157 /* Fire the CPG Purchasing Synchronization Concurrent Request */
158 GML_PO_CON_REQ.fire_request;
159
160 END IF;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 raise_application_error(-20000,SQLERRM);
165 END;
166
167 END GML_PO_HEADER_ARCHIVE;