[Home] [Help]
129: end;
130: --
131: FUNCTION p_psg_exists(p_segment_id in number,p_pte_code in varchar2)
132: return number is
133: --Private function to check if given Segment and PTE exist in QP_PTE_SEGMENTS table.
134: dummy varchar2(1);
135: begin
136: select 'x'
137: into dummy
134: dummy varchar2(1);
135: begin
136: select 'x'
137: into dummy
138: from qp_pte_segments
139: where pte_code = p_pte_code and
140: segment_id = p_segment_id and
141: rownum = 1;
142: return(0);
324: dummy varchar2(1);
325: begin
326: select 'x'
327: into dummy
328: from qp_pte_segments
329: where segment_id = p_segment_id and
330: pte_code = 'ONT' and
331: rownum = 1;
332: return(0);
726: p_pte_code in varchar2,
727: p_segment_level in varchar2,
728: p_sourcing_method in varchar2,
729: p_sourcing_enabled in varchar2) is
730: -- Private procedure to insert PTE-Segments in qp_pte_segments.
731: begin
732: g_psg_seqno := g_psg_seqno + 1;
733: INSERT into QP_PTE_SEGMENTS
734: (SEGMENT_PTE_ID,
729: p_sourcing_enabled in varchar2) is
730: -- Private procedure to insert PTE-Segments in qp_pte_segments.
731: begin
732: g_psg_seqno := g_psg_seqno + 1;
733: INSERT into QP_PTE_SEGMENTS
734: (SEGMENT_PTE_ID,
735: SEGMENT_ID,
736: PTE_CODE,
737: SEGMENT_LEVEL,
879: /*
880: --
881: -- 1. For LOGISTICS context and PTEs other than 'Logistics',
882: --
883: delete from qp_pte_segments a
884: where a.pte_code <> 'LOGSTX' and
885: exists ( select 'x'
886: from qp_segments_b b,
887: qp_prc_contexts_b c
891: a.created_by = 1;
892: --
893: -- 2. COUPON_NO attribute and 'Logistics' PTE
894: --
895: delete from qp_pte_segments a
896: where a.pte_code = 'LOGSTX' and
897: exists ( select 'x'
898: from qp_segments_b b
899: where b.segment_id = a.segment_id and
901: a.created_by = 1;
902: --
903: -- 3. 'Number of Students' attribute and PTEs other than 'Order Fulfillment'.
904: --
905: delete from qp_pte_segments a
906: where a.pte_code <> 'ORDFUL' and
907: exists ( select 'x'
908: from qp_segments_b b
909: where b.segment_id = a.segment_id and
913: --
914: -- 4. Links having Attribute Mapping Method as ATTRIBUTE MAPPING
915: -- and PTE as INTCOM without any Attribute Mapping rules.
916: --
917: delete from qp_pte_segments a
918: where a.pte_code = 'INTCOM' and
919: a.seeded_sourcing_method = 'ATTRIBUTE MAPPING' and
920: not exists ( select 'x'
921: from qp_attribute_sourcing b,
927: --
928: -- 5. Links having Attribute Mapping Method as ATTRIBUTE MAPPING
929: -- and PTE as DEMAND without any Attribute Mapping rules.
930: --
931: delete from qp_pte_segments a
932: where a.pte_code = 'DEMAND' and
933: a.seeded_sourcing_method = 'ATTRIBUTE MAPPING' and
934: not exists ( select 'x'
935: from qp_attribute_sourcing b,
1001: where pte_source_system_id < 100000;
1002: --
1003: select nvl(max(segment_pte_id),100)
1004: into g_psg_seqno
1005: from qp_pte_segments
1006: where segment_pte_id < 100000;
1007: --
1008: select nvl(max(attribute_sourcing_id),100)
1009: into g_source_seqno