[Home] [Help]
114:
115: --Added the following cursors start*/
116: CURSOR c_gst_ass_value_cust(p_party_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_uom_code VARCHAR2, p_ordered_date DATE) IS
117: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
118: FROM qp_list_lines b, qp_pricing_attributes c
119: WHERE c.list_line_id = b.list_line_id
120: AND c.product_attr_value = to_char(p_inventory_item_id)
121: AND c.product_uom_code = p_uom_code
122: AND p_ordered_date BETWEEN
135: AND nvl(qlh.active_flag, 'N') = 'Y');
136:
137: CURSOR c_gst_ass_value_pri_uom_cust(p_party_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_ordered_date DATE) IS
138: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
139: FROM qp_list_lines b, qp_pricing_attributes c
140: WHERE c.list_line_id = b.list_line_id
141: AND c.product_attr_value = to_char(p_inventory_item_id)
142: AND trunc(nvl(b.end_date_active, SYSDATE)) >=
143: trunc(p_ordered_date)
154: AND nvl(primary_uom_flag, 'N') = 'Y';
155:
156: CURSOR c_gst_ass_value_other_uom_cust(p_party_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_ordered_date DATE) IS
157: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
158: FROM qp_list_lines b, qp_pricing_attributes c
159: WHERE c.list_line_id = b.list_line_id
160: AND c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id)
161: AND NVL(b.end_date_active, SYSDATE) >= p_ordered_date
162: AND EXISTS
179: */
180: CURSOR c_gst_ass_value_vend(p_vendor_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_uom_code VARCHAR2, p_ordered_date DATE) IS
181: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
182: FROM JAI_CMN_VENDOR_SITES a,
183: qp_list_lines b,
184: qp_pricing_attributes c
185: WHERE a.vendor_id = p_vendor_id
186: AND a.vendor_site_id = p_address_id
187: AND a.Gst_Assessable_Price = b.LIST_header_ID
198:
199: CURSOR c_gst_ass_value_pri_uom_vend(p_vendor_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_ordered_date DATE) IS
200: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
201: FROM JAI_CMN_VENDOR_SITES a,
202: qp_list_lines b,
203: qp_pricing_attributes c
204: WHERE a.vendor_id = p_vendor_id
205: AND a.vendor_site_id = p_address_id
206: AND a.Gst_Assessable_Price = b.list_header_id
212:
213: CURSOR c_gst_ass_value_other_uom_vend(p_vendor_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_ordered_date DATE) IS
214: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
215: FROM JAI_CMN_VENDOR_SITES a,
216: qp_list_lines b,
217: qp_pricing_attributes c
218: WHERE a.vendor_id = p_vendor_id
219: AND a.vendor_site_id = p_address_id
220: AND a.Gst_Assessable_Price = b.LIST_header_ID
250: --Get the gst Assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, Ordered date.
251: CURSOR cust_ass_value_category_cur(pn_party_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pv_uom_code VARCHAR2, pd_ordered_date DATE) IS
252: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
253: FROM jai_cmn_cus_addresses a,
254: qp_list_lines b,
255: qp_pricing_attributes c
256: WHERE a.customer_id = pn_party_id
257: AND a.address_id = pn_address_id
258: AND a.Gst_Assessable_Price = b.list_header_id
271: --Get the gst Assessable Value based on the Primary Uom, Customer Id, Address Id, inventory_item_id, Ordered date.
272: CURSOR cust_ass_value_pri_uom_cur(pn_party_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pd_ordered_date DATE) IS
273: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
274: FROM jai_cmn_cus_addresses a,
275: qp_list_lines b,
276: qp_pricing_attributes c
277: WHERE a.customer_id = pn_party_id
278: AND a.address_id = pn_address_id
279: AND a.Gst_Assessable_Price = b.list_header_id
291: --Get the gst Assessable Value based on the Customer Id, Address Id, inventory_item_id, Ordered date.
292: CURSOR cust_ass_value_other_uom_cur(pn_party_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pd_ordered_date DATE) IS
293: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
294: FROM jai_cmn_cus_addresses a,
295: qp_list_lines b,
296: qp_pricing_attributes c
297: WHERE a.customer_id = pn_party_id
298: AND a.address_id = pn_address_id
299: AND a.Gst_Assessable_Price = b.list_header_id
310: -- Get the gst Assessable Value based on the Vendor Id, Address Id, inventory_item_id, uom code, Ordered date.
311: CURSOR vend_ass_value_category_cur(pn_vendor_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pv_uom_code VARCHAR2, pd_ordered_date DATE) IS
312: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
313: FROM jai_cmn_vendor_sites a,
314: qp_list_lines b,
315: qp_pricing_attributes c
316: WHERE a.vendor_id = pn_vendor_id
317: AND a.vendor_site_id = pn_address_id
318: AND a.Gst_Assessable_Price = b.list_header_id
331: -- Get the gst Assessable Value based on the Primary Uom, Vendor Id, Address Id, inventory_item_id, Ordered date.
332: CURSOR vend_ass_value_pri_uom_cur(pn_vendor_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pd_ordered_date DATE) IS
333: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
334: FROM jai_cmn_vendor_sites a,
335: qp_list_lines b,
336: qp_pricing_attributes c
337: WHERE a.vendor_id = pn_vendor_id
338: AND a.vendor_site_id = pn_address_id
339: AND a.Gst_Assessable_Price = b.list_header_id
351: -- Get the gst Assessable Value based on the Vendor Id, Address Id, inventory_item_id, Ordered date.
352: CURSOR vend_ass_value_other_uom_cur(pn_vendor_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pd_ordered_date DATE) IS
353: SELECT b.operand list_price, c.product_uom_code list_price_uom_code
354: FROM jai_cmn_vendor_sites a,
355: qp_list_lines b,
356: qp_pricing_attributes c
357: WHERE a.vendor_id = pn_vendor_id
358: AND a.vendor_site_id = pn_address_id
359: AND a.Gst_Assessable_Price = b.list_header_id