[Home] [Help]
PACKAGE BODY: APPS.GMF_SYNC_VENDOR
Source
1 package body gmf_sync_vendor as
2 /* $Header: gmfvnsyb.pls 115.5 2002/12/04 17:04:34 umoogala ship $ */
3 procedure gmf_sync_vendor(error_buf out nocopy varchar2,
4 retcode out nocopy number,
5 p_co_code in varchar2) is
6 /* ---------------------
7 -- Declare variables
8 ---------------------- */
9 v_last_update_date date;
10 v_total_vendors_synched number:=0;
11 v_org_id number;
12 v_gl_log_trigger_cache number;
13
14 --Begin Bug 1677297 Mohit Kapoor
15 --Added following variables
16 v_vendor_id PO_VENDOR_SITES_ALL.VENDOR_ID%Type;
17 v_vendor_site_code PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE%Type;
18 v_segment1 PO_VENDORS.SEGMENT1%Type;
19 v_total_vendors_failed number:=0;
20 v_total_vendors number:=0;
21 --End Bug 1677297
22
23 /*--------------------
24 -- Declare cursor
25 --------------------*/
26 cursor c_gl_plcy_mst(p_co_code varchar2) is
27 select decode(upper(f.multi_org_flag), 'Y', p.org_id, NULL)
28 from gl_plcy_mst p,
29 fnd_product_groups f
30 where p.co_code=p_co_code;
31
32 --Begin Bug 1677297 Mohit Kapoor
33 -- Changed cursor definition added vendor_id, vendor_site_code
34 cursor c_po_vendor_sites_all(p_co_code varchar2) is
35 select i.last_update_date, i.vendor_id, i.vendor_site_code
36 from po_vendor_sites_all i
37 where nvl(i.org_id,-999) = nvl(v_org_id,-999)
38 for update of last_update_date;
39 --End Bug 1677297 Mohit Kapoor
40
41 --Begin Bug 1677297 Mohit Kapoor Added cursor
42 cursor c_po_vendors(p_vendor_id Number) is
43 select v.segment1
44 from po_vendors v
45 where v.vendor_id = p_vendor_id;
46 -- End Bug 1677297
47
48 begin
49 /*-----------------------------------------------------------------
50 -- Cache the value of GL_LOG_TRIGGER_EXCEPTION to reset it in end.
51 -----------------------------------------------------------------*/
52 Gmf_sync_init.Glsynch_Initialize;
53 v_gl_log_trigger_cache:=gmf_session_vars.gl_log_trigger_error;
54 gmf_session_vars.gl_log_trigger_error:=1;
55
56 --Begin Bug 1677297 Mohit Kapoor
57 PRINT_LINE ('Starting concurrent program execution ' ||to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'));
58 PRINT_LINE ('COMPANY : '||p_co_code);
59 PRINT_LINE ('');
60 PRINT_LINE ( '-----------------------------------------------------------------');
61 PRINT_LINE ('Vendor :-');
62 --End Bug 1677297
63
64 if (c_gl_plcy_mst%ISOPEN) then
65 close c_gl_plcy_mst;
66 end if;
67
68 open c_gl_plcy_mst(p_co_code);
69 fetch c_gl_plcy_mst into v_org_id;
70 close c_gl_plcy_mst;
71
72 if (c_po_vendor_sites_all%ISOPEN) then
73 close c_po_vendor_sites_all;
74 end if;
75
76 open c_po_vendor_sites_all(v_org_id);
77 loop
78 --Begin Bug1677297 Mohit Kapoor
79 Gmf_Session_Vars.FOUND_ERRORS := 'N';
80
81 -- Modified Fetch statement 1677297
82 fetch c_po_vendor_sites_all into v_last_update_date,v_vendor_id ,v_vendor_site_code ;
83 exit when c_po_vendor_sites_all%NOTFOUND;
84
85 GMF_Session_Vars.GL$VEND_DELIMITER := nvl(FND_PROFILE.VALUE('GL$VEND_DELIMITER'),'-');
86
87 open c_po_vendors(v_vendor_id);
88 fetch c_po_vendors into v_segment1;
89 close c_po_vendors;
90
91 PRINT (' '|| v_segment1 || GMF_Session_Vars.GL$VEND_DELIMITER || v_vendor_site_code );
92 v_total_vendors:=v_total_vendors+1;
93 --End Bug 1677297
94
95 /*---------------------------------------------------
96 -- This update will trigger vendor trigger to synch
97 ---------------------------------------------------*/
98 update po_vendor_sites_all
99 set last_update_date=last_update_date
100 where current of c_po_vendor_sites_all;
101
102 --Begin Bug1677297 Mohit Kapoor
103 -- Added code
104 if Gmf_Session_Vars.FOUND_ERRORS = 'N' then
105 PRINT_LINE ('- Processed - Success ! '||to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'));
106 v_total_vendors_synched := v_total_vendors_synched + 1;
107 elsif Gmf_Session_Vars.FOUND_ERRORS = 'Y' then
108 PRINT_LINE ('- Failed '||to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'));
109 v_total_vendors_failed := v_total_vendors_failed+1;
110 end if;
111 --End Bug 1677297
112
113 end loop;
114 close c_po_vendor_sites_all;
115 --Begin Bug1677297 Mohit Kapoor
116 PRINT_LINE ( '-----------------------------------------------------------------');
117 PRINT_LINE ('Records Failed = ' ||to_char(v_total_vendors_failed));
118 PRINT_LINE ('Total Records Synched = ' || to_char(v_total_vendors_synched));
119 PRINT_LINE ('Total Records = ' || to_char(v_total_vendors));
120 --End Bug 1677297
121
122 /*--------------------------------------------------------
123 -- Reset the Global pkg variable back to the cached value
124 --------------------------------------------------------*/
125 gmf_session_vars.gl_log_trigger_error:=v_gl_log_trigger_cache;
126 retcode:=0;
127 exception
128 when others then
129 fnd_message.set_name('GMF','GL_TRIGGER_ERROR');
130 fnd_message.set_token('TRIGGER_NAME',substrb('GMF_SYNC_VENDOR-'||to_char(SQLCODE)||' '||SQLERRM,1,512));
131 fnd_file.put_line(fnd_file.log,fnd_message.get);
132 retcode:=2;
133 end;
134
135
136 /* REM Wrapper for printing */
137 /* Begin Bug1677297 Mohit Kapoor */
138 PROCEDURE PRINT_LINE
139 (line_text IN VARCHAR2) IS
140 BEGIN
141 FND_FILE.PUT_LINE ( FND_FILE.OUTPUT,line_text);
142 END;
143
144 PROCEDURE PRINT
145 (line_text IN VARCHAR2) IS
146 BEGIN
147 FND_FILE.PUT ( FND_FILE.OUTPUT,line_text);
148 END;
149 /* End Bug1677297 */
150 end gmf_sync_vendor;