Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2012
    Posts
    7

    Unanswered: Tune the procedure

    Hi Guys,

    I had written one procedure, But it was taking so long time like 4 hrs to complete.

    it's around processing 6967439 records.

    Is there any possiblity to tune the below code like i'm doing insert each record by record.

    Can't i do it Bulk insert with same logic ?

    Please advise me on this

  2. #2
    Join Date
    Jun 2012
    Posts
    7
    PROCEDURE test_sync_dw_CREDIT_fact_isp (DEBUG VARCHAR2 DEFAULT 'N') AS
    v_version_number VARCHAR2(10) := 'v1.10';
    proc_name VARCHAR2(50) := 'test_sync_dw_CREDIT_fact_isp'||CHR(32)||v_version _number;
    marker VARCHAR2(4);
    set_start_time DATE;
    set_last_run_date DATE;
    set_account_id test_CREDIT_FACT.test_account_id%TYPE;
    update_cnt NUMBER(8) :=0;
    insert_cnt NUMBER(8) :=0;
    invoice_cnt NUMBER(8) :=0;
    commit_cnt NUMBER(8) :=0;
    perf_ref NUMBER;
    v_invoice_with_product VARCHAR2(1);
    n_total_volume NUMBER;
    n_total_packed_volume NUMBER;
    n_total_price_adjustment_sur NUMBER;
    n_total_port_diff NUMBER;
    n_total_pack_diff NUMBER;
    n_total_other_sur NUMBER;
    n_total_other_del_disc NUMBER;
    n_total_tech_offer NUMBER;
    n_total_non_compliance_cred NUMBER;
    n_total_misc_charges NUMBER;
    n_total_other_credits NUMBER;
    n_total_price_adj_disc NUMBER;
    n_total_non_prod_rebate NUMBER;
    n_volume NUMBER;
    n_packed_volume NUMBER;
    n_price_adjustment_sur NUMBER;
    n_port_diff NUMBER;
    n_pack_diff NUMBER;
    n_other_sur NUMBER;
    n_other_del_disc NUMBER;
    n_tech_offer NUMBER;
    n_non_compliance_cred NUMBER;
    n_misc_charges NUMBER;
    n_other_credits NUMBER;
    n_price_adj_disc NUMBER;
    n_non_prod_rebate NUMBER;
    set_id test_DW_CREDIT_FACT.id%TYPE;
    set_transaction_ref test_DW_CREDIT_FACT.transaction_ref%TYPE;
    set_test_product_id test_DW_CREDIT_FACT.test_product_id%TYPE;
    set_test_condition_type_id test_DW_CREDIT_FACT.test_condition_type_id%TYPE;
    set_test_account_id test_DW_CREDIT_FACT.test_account_id%TYPE;
    set_test_vessel_id test_DW_CREDIT_FACT.test_vessel_id%TYPE;
    set_test_location_id test_DW_CREDIT_FACT.test_location_id%TYPE;
    set_test_stock_point_id test_DW_CREDIT_FACT.test_stock_point_id%TYPE;
    set_test_source_system_id test_DW_CREDIT_FACT.test_source_system_id%TYPE;
    set_delivery_date test_DW_CREDIT_FACT.delivery_date%TYPE;
    set_invoice_date test_DW_CREDIT_FACT.invoice_date%TYPE;
    set_period_num_acctg test_DW_CREDIT_FACT.period_num_acctg%TYPE;
    set_amount test_DW_CREDIT_FACT.amount%TYPE;
    set_created_date test_DW_CREDIT_FACT.created_date%TYPE;
    set_created_by test_DW_CREDIT_FACT.created_by%TYPE;
    set_updated_date test_DW_CREDIT_FACT.updated_date%TYPE;
    set_updated_by test_DW_CREDIT_FACT.updated_by%TYPE;
    set_vessel_name test_DW_CREDIT_FACT.vessel_name%TYPE;
    set_purchase_order_number test_DW_CREDIT_FACT.purchase_order_number%TYPE;
    set_manta_number test_DW_CREDIT_FACT.manta_number%TYPE;
    set_ord_num test_DW_CREDIT_FACT.ord_num%TYPE;
    set_manta_order_date test_DW_CREDIT_FACT.manta_order_date%TYPE;
    set_invoice_currency test_DW_CREDIT_FACT.invoice_currency%TYPE;
    set_exchange_rate_inv_to_local test_DW_CREDIT_FACT.exchange_rate_inv_to_local%TYP E;
    set_local_currency test_DW_CREDIT_FACT.local_currency%TYPE;
    set_exchange_rate_lcl_to_usd test_DW_CREDIT_FACT.exchange_rate_lcl_to_usd%TYPE;
    set_contra_ind test_DW_CREDIT_FACT.contra_ind%TYPE;
    set_inv_status test_DW_CREDIT_FACT.inv_status%TYPE;
    set_payment_due_date test_DW_CREDIT_FACT.payment_due_date%TYPE;
    set_ar_due_date test_DW_CREDIT_FACT.ar_due_date%TYPE;
    set_payment_date test_DW_CREDIT_FACT.payment_date%TYPE;
    set_business_type test_DW_CREDIT_FACT.business_type%TYPE;
    set_trade test_DW_CREDIT_FACT.trade%TYPE;
    set_ar_ind test_DW_CREDIT_FACT.ar_ind%TYPE;
    set_data_quality_ind test_DW_CREDIT_FACT.data_quality_ind%TYPE;
    --1.5 start of changes
    set_investment_id test_DW_CREDIT_FACT.investment_id%TYPE;
    set_rebate_id test_DW_CREDIT_FACT.rebate_id%TYPE;
    --1.5 end of changes
    --1.6 start of changes
    set_contract_assignment test_DW_CREDIT_FACT.contract_assignment%TYPE;
    set_dlvy_note_num test_DW_CREDIT_FACT.dlvy_note_num%TYPE;
    set_distr_chan test_DW_CREDIT_FACT.distr_chan%TYPE;
    set_division test_DW_CREDIT_FACT.division%TYPE;
    set_CREDITorg test_DW_CREDIT_FACT.CREDITorg%TYPE;
    set_test_segment_id test_DW_CREDIT_FACT.test_segment_id%TYPE; -- 1.9
    set_subsea_ind VARCHAR2(1); -- 1.9
    -- 1.6 end of changes
    SET_ORIGINAL_INVOICE_NUMBER test_DW_CREDIT_FACT. ORIGINAL_INVOICE_NUMBER %TYPE; --1.8

    -- this cursor will pick-up all invoices that have changed in test_CREDIT_fact since the last time this procedure was run
    CURSOR changed_invoices IS
    SELECT DISTINCT transaction_ref, test_account_id
    FROM test_CREDIT_FACT sf, test_ACCOUNT a
    WHERE sf.test_account_id = a.id
    -- and sf.trade = 'L' commented out as part of 1.6 version
    AND sf.transaction_ref NOT LIKE 'USBO%'
    -- and nvl(a.intra_company,'N') <> 'Y'
    -- and a.account_name <> 'L-BALTIC PET PURCHASING'
    -- and nvl(sf.data_quality_ind,0) not in (3, 4)
    AND NVL(a.brand_offer,'XXX') <> 'BP Fuels'
    AND transaction_ref IS NOT NULL
    AND sf.period_num_acctg >= '200701'
    AND (sf.updated_date > set_start_time OR sf.created_date > set_start_time OR data_quality_ind IS NOT NULL);

    -- this cursor is called for each invoice returned by above cursor. It returns each invoice line for the given invoice number.
    CURSOR invoice_lines IS
    SELECT sf.transaction_ref, sf.test_account_id, sf.del_test_product_id, sf.del_test_loc_id, sf.test_vessel_id, sf.del_date, sf.invoice_date, sf.period_num_acctg,
    p.test_waterfall_element_id, art_type_mnmc, p.mtl_art_name,
    sf.vessel_name, sf.purchase_order_number, sf.manta_number, sf.ord_num, sf.manta_order_date, sf.local_currency, sf.exchange_rate_to_rcu,
    sf.contra_ind, DECODE(sf.inv_status,2,2,GREATEST(sf.csm_status,sf .inv_status)) contra_seq, sf.business_type, sf.trade,
    sf.payment_date, sf.ar_due_date, sf.ar_ind, sf.test_stock_point_id,payment_due_date,data_quali ty_ind,
    SUM(NVL(std_vol,0)) volume,
    SUM((sf.invoice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)-NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))) inv_val,
    SUM(NVL(sf.amortisation*-1,0)) amortisation,
    SUM(DECODE(NVL(sf.cogs_status,1),
    -- 1 means we we have an oil cost from isp so check data quality ind
    1, DECODE(NVL(sf.data_quality_ind,9),
    -- 1 means problem with isp oil cost so use avg cogs
    1, NVL(sf.avg_cogs,0)/NVL(sf.exchange_rate_to_rcu,1), -- divide avg cogs that are always USD by isp exch rate to convert to local currency
    -- else use isp oil cost
    NVL(sf.oil_cost,0)),
    -- else no oil cost so use avg cogs
    NVL(sf.avg_cogs,0)/NVL(sf.exchange_rate_to_rcu,1))) cogs, -- divide avg cogs that are always USD by isp exch rate to convert to local currency
    SUM(NVL(sf.discount*-1,0)) discount,
    SUM(NVL(ivr_provision,0)) rebate,
    SUM(NVL(sf.logistics_rate*NVL(sf.logistics_exch_ra te,1),0)*NVL(sf.std_vol,0)/NVL(sf.exchange_rate_to_rcu,1)) logistics, -- divide by isp exch rate to convert to local currency
    -- SUM(NVL(sf.ivr_capitalisation_adjustment,0)) cap_adjustment, Commented out as part 1.10 changes
    SUM(NVL(sf.ivr_capitalisation_adjustment/nvl(sf.exchange_rate_to_rcu,1),0)) cap_adjustment, -- Modified as part of 1.10 changes
    SUM(NVL(ap.cosa,0)*NVL(sf.std_vol,0)) cosa,
    --1.5 start of changes
    sf.test_ivr_investment_id,
    sf.test_ivr_rebate_id,
    --1.5 end of changes
    --1.6 start of changes
    sf.contract_assignment,
    sf.dlvy_note_num,
    DECODE(sf.trade,'F','01','L','02',NULL) AS division,
    --1.6 end of changes
    NVL(sf.test_segment_id,0) test_segment_id, -- 1.9
    NVL(sf.test_source_system_id,1) test_source_system_id -- 1.9
    FROM test_CREDIT_FACT sf, test_PRODUCT p, test_ACCOUNT_PERIOD ap
    WHERE sf.del_test_product_id = p.id
    AND sf.period_num_acctg = ap.period_num
    AND sf.transaction_ref = set_transaction_ref
    AND sf.test_account_id = set_account_id
    GROUP BY sf.transaction_ref, sf.test_account_id, sf.del_test_product_id, sf.del_test_loc_id, sf.test_vessel_id, sf.del_date, sf.invoice_date, sf.period_num_acctg,
    p.test_waterfall_element_id, art_type_mnmc, p.mtl_art_name,
    sf.vessel_name, sf.purchase_order_number, sf.manta_number, sf.ord_num, sf.manta_order_date, sf.local_currency, sf.exchange_rate_to_rcu,
    sf.contra_ind, DECODE(sf.inv_status,2,2,GREATEST(sf.csm_status,sf .inv_status)), sf.business_type, sf.trade,
    sf.payment_date, sf.ar_due_date, sf.ar_ind, sf.test_stock_point_id,payment_due_date,data_quali ty_ind,
    --1.5 start of changes
    sf.test_ivr_investment_id,sf.test_ivr_rebate_id,sf .contract_assignment,sf.dlvy_note_num,sf.trade,
    --1.5 end of changes
    NVL(sf.test_segment_id,0), -- 1.9
    NVL(sf.test_source_system_id,1) -- 1.9
    ;

  3. #3
    Join Date
    Jun 2012
    Posts
    7
    PROCEDURE insert_dw_CREDIT_fact
    AS
    n_new_id test_DW_CREDIT_FACT.id%TYPE;
    BEGIN

    --1.4 Srikanth 22-Apr-2010 Commented code as this is not required after 1.4
    -- select test_sequence.nextval
    -- into n_new_id
    -- from dual;
    --1.4 End of the changes
    INSERT /*+ APPEND */ INTO test_DW_CREDIT_FACT (
    id,
    transaction_ref,
    test_product_id,
    test_condition_type_id,
    test_account_id,
    test_vessel_id,
    test_location_id,
    test_stock_point_id,
    test_source_system_id,
    delivery_date,
    invoice_date,
    period_num_acctg,
    amount,
    created_date,
    created_by,
    updated_date,
    updated_by,
    vessel_name,
    purchase_order_number,
    manta_number,
    ord_num,
    manta_order_date,
    invoice_currency,
    exchange_rate_inv_to_local,
    local_currency,
    exchange_rate_lcl_to_usd,
    contra_ind,
    inv_status,
    payment_due_date,
    ar_due_date,
    payment_date,
    business_type,
    trade,
    ar_ind,
    data_quality_ind,
    --1.5 start of changes
    investment_id,
    rebate_id,
    --1.5 end of changes
    --1.6 start of changes
    contract_assignment,
    dlvy_note_num,
    distr_chan,
    division,
    CREDITorg
    --1.6 end of changes
    ,original_invoice_number -- 1.8 Change
    , test_segment_id -- 1.9
    )
    VALUES (

    1,
    set_transaction_ref,
    set_test_product_id,
    set_test_condition_type_id,
    set_test_account_id,
    set_test_vessel_id,
    set_test_location_id,
    set_test_stock_point_id,
    -- 1, -- 1.9
    set_test_source_system_id, -- 1.9
    set_delivery_date,
    set_invoice_date,
    set_period_num_acctg,
    set_amount,
    SYSDATE,
    proc_name,
    set_updated_date,
    set_updated_by,
    set_vessel_name,
    set_purchase_order_number,
    set_manta_number,
    set_ord_num,
    set_manta_order_date,
    set_invoice_currency,
    set_exchange_rate_inv_to_local,
    set_local_currency,
    set_exchange_rate_lcl_to_usd,
    set_contra_ind,
    set_inv_status,
    set_payment_due_date,
    set_ar_due_date,
    set_payment_date,
    set_business_type,
    set_trade,
    set_ar_ind,
    set_data_quality_ind,
    --1.5 start of changes
    set_investment_id,
    set_rebate_id,
    --1.5 end of changes
    --1.6 start of changes
    set_contract_assignment,
    set_dlvy_note_num,
    set_distr_chan,
    set_division,
    set_CREDITorg
    --1.6 End of changes
    ,SET_ORIGINAL_INVOICE_NUMBER --1.8 Change
    , set_test_segment_id -- 1.9
    );

    insert_cnt := insert_cnt + 1;

    END;

    ---------------------
    -- Executable part
    ---------------------
    BEGIN

    dbms_output.put_line(proc_name);
    perf_ref := test_log_performance (proc_name, 'START', 0);
    dbms_output.put_line('perf_ref: '||perf_ref);

    ------------------------------------------
    -- Get last date that the procedure ran
    -------------------------------------------
    marker := '0100';
    SELECT last_run_date
    INTO set_start_time
    FROM test_MGT_LAST_RUN_DATE
    WHERE procedure_name = 'test_sync_dw_CREDIT_fact_isp';

    -- record the date it is running now
    marker := '0110';
    SELECT SYSDATE INTO set_last_run_date FROM dual;

    -- set defaults for any new products
    marker := '0120';
    UPDATE test_PRODUCT SET
    -- test_waterfall_element_id = DECODE(art_type_mnmc,'BL',9999,'PK',9999,'CH',4,'E Q',6,'FE',4,'NK',9999,'SU',4,'SV',4,9), -- 1.9
    test_waterfall_element_id = DECODE(art_type_mnmc,'BL',9999,'PK',9999,'CH',4,'E Q',6,'FE',4,'NK',9999,'SU',4,'SV',4,'O',9999,'NO', 4,9), -- 1.9
    wf_review_ind = 'N'
    WHERE (test_waterfall_element_id IS NULL OR test_waterfall_element_id = 9);
    COMMIT;

    -----------------------------------------------------------------------------------------
    -- Begin loop which retrieves changed invoices from test_CREDIT_fact
    -----------------------------------------------------------------------------------------
    FOR c IN changed_invoices LOOP

  4. #4
    Join Date
    Jun 2012
    Posts
    7
    marker := '0200';
    invoice_cnt := invoice_cnt + 1;
    set_transaction_ref := c.transaction_ref;
    set_account_id := c.test_account_id;

    IF DEBUG = 'Y' THEN
    dbms_output.put_line(set_transaction_ref);
    END IF;

    -- delete waterfall fact records for this invoice
    BEGIN
    marker := '0210';
    DELETE
    FROM test_DW_CREDIT_FACT wf
    WHERE wf.transaction_ref = c.transaction_ref
    AND wf.test_account_id = c.test_account_id;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Failed to delete transactions from test_dw_CREDIT_fact. Transaction ref: '||c.transaction_ref);
    RAISE;
    END;

    -- get totals for the invoice of non-product lines that we want to allocate to product lines
    BEGIN
    marker := '0220';
    -- initialise variables
    v_invoice_with_product := 'N';
    n_total_volume := 0;
    n_total_packed_volume := 0;
    n_total_price_adjustment_sur := 0;
    n_total_port_diff := 0;
    n_total_pack_diff := 0;
    n_total_other_sur := 0;
    n_total_other_del_disc := 0;
    n_total_tech_offer := 0;
    n_total_non_compliance_cred := 0;
    n_total_misc_charges := 0;
    n_total_other_credits := 0;
    n_total_price_adj_disc := 0;
    n_total_non_prod_rebate := 0;
    marker := '0300';
    SELECT
    -- does invoice have a product on it?
    DECODE(MAX(p.test_waterfall_element_id),9999,'Y',' N'),
    -- total vol
    SUM(std_vol),
    -- packed volume
    SUM(DECODE(p.art_type_mnmc,'PK',sf.std_vol,0)),
    --price adjustment surcharge
    SUM(DECODE(p.test_waterfall_element_id,1,((sf.invo ice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --port differential
    SUM(DECODE(p.test_waterfall_element_id,2,((sf.invo ice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --pack differential
    SUM(DECODE(p.test_waterfall_element_id,3,((sf.invo ice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --other surcharge
    SUM(DECODE(p.test_waterfall_element_id,4,((sf.invo ice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --other delivery discount
    SUM(DECODE(p.test_waterfall_element_id,5,((sf.invo ice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --technical & offer charges
    SUM(DECODE(p.test_waterfall_element_id,6,((sf.invo ice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --non-compliance credits
    SUM(DECODE(p.test_waterfall_element_id,7,((sf.invo ice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --miscelleneous charges
    SUM(DECODE(p.test_waterfall_element_id,9,((sf.invo ice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --other credit notes
    SUM(DECODE(p.test_waterfall_element_id,10,((sf.inv oice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --price adjustment discount
    SUM(DECODE(p.test_waterfall_element_id,11,((sf.inv oice_value-NVL(sf.MOT,0)-NVL(sf.CREDIT_TAX,0)
    -NVL(sf.OTHER_MOT_VAL,0)-NVL(sf.OTHER_CREDIT_TAX_VAL,0))),0)),
    --rebate on non-product lines
    SUM(DECODE(p.test_waterfall_element_id,9999,0,(NVL (sf.ivr_provision,0))))
    INTO
    v_invoice_with_product,
    n_total_volume,
    n_total_packed_volume,
    n_total_price_adjustment_sur,
    n_total_port_diff,
    n_total_pack_diff,
    n_total_other_sur,
    n_total_other_del_disc,
    n_total_tech_offer,
    n_total_non_compliance_cred,
    n_total_misc_charges,
    n_total_other_credits,
    n_total_price_adj_disc,
    n_total_non_prod_rebate
    FROM test_CREDIT_FACT sf, test_PRODUCT p
    WHERE sf.del_test_product_id = p.id
    AND sf.transaction_ref = c.transaction_ref
    AND sf.test_account_id = c.test_account_id;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Failed to get total values for transaction: '||set_transaction_ref);
    RAISE;
    END;

    IF DEBUG = 'Y' THEN
    dbms_output.put_line('total vol '||n_total_volume);
    dbms_output.put_line('total port diff '||n_total_port_diff);
    dbms_output.put_line('total pack diff '||n_total_pack_diff);
    END IF;

    ------------------------------------------------------------------------------------
    -- retrieve each invoice line for the given invoice
    -- allocate non-product amounts to product lines in proportion to volume
    -- insert records into test_dw_CREDIT_fact
    ------------------------------------------------------------------------------------

  5. #5
    Join Date
    Jun 2012
    Posts
    7
    marker := '0350';

    FOR c2 IN invoice_lines LOOP
    marker := '0360';
    -- initialise variables
    n_volume := 0;
    n_packed_volume := 0;
    n_price_adjustment_sur := 0;
    n_port_diff := 0;
    n_pack_diff := 0;
    n_other_sur := 0;
    n_other_del_disc := 0;
    n_tech_offer := 0;
    n_non_compliance_cred := 0;
    n_misc_charges := 0;
    n_other_credits := 0;
    n_price_adj_disc := 0;
    n_non_prod_rebate := 0;
    -- allocate non-product values to product line
    IF DEBUG = 'Y' THEN
    dbms_output.put_line('mtl_art '||c2.mtl_art_name);
    END IF;

    --price adjustment surcharge
    marker := '0400';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_price_adjustment_sur := n_total_price_adjustment_sur/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_price_adjustment_sur := n_total_price_adjustment_sur;
    END IF;

    -- port differential
    marker := '0410';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_port_diff := n_total_port_diff/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_port_diff := n_total_port_diff;
    END IF;

    -- pack differential
    IF c2.art_type_mnmc = 'PK' THEN
    marker := '0420';
    IF NVL(n_total_packed_volume,1) <> 0 THEN
    n_pack_diff := n_total_pack_diff/n_total_packed_volume*c2.volume;
    ELSE
    n_pack_diff := n_total_pack_diff;
    END IF;
    END IF;
    IF c2.art_type_mnmc = 'BL' AND n_total_pack_diff <> 0 AND n_total_packed_volume = 0 THEN -- we have a pack diff to allocate but not packed volume we need to allocate dto all oily lines
    marker := '0430';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_pack_diff := n_total_pack_diff/n_total_volume*c2.volume;
    ELSE
    n_pack_diff := n_total_pack_diff;
    END IF;
    END IF;

    -- other surcharge
    marker := '0450';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_other_sur := n_total_other_sur/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_other_sur := n_total_other_sur;
    END IF;

    -- other delivery discount
    marker := '0400';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_other_del_disc := n_total_other_del_disc/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_other_del_disc := n_total_other_del_disc;
    END IF;

    -- tech offer
    marker := '0460';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_tech_offer := n_total_tech_offer/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_tech_offer := n_total_tech_offer;
    END IF;

    -- non compliance rebate
    marker := '0470';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_non_compliance_cred := n_total_non_compliance_cred/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_non_compliance_cred := n_total_non_compliance_cred*-1;
    END IF;

    -- misc charges
    marker := '0480';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_misc_charges := n_total_misc_charges/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_misc_charges := n_total_misc_charges;
    END IF;

    -- other credits
    marker := '0490';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_other_credits := n_total_other_credits/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_other_credits := n_total_other_credits*-1;
    END IF;

    -- price adjustment discount
    marker := '0491';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_price_adj_disc := n_total_price_adj_disc/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_price_adj_disc := n_total_price_adj_disc;
    END IF;

    -- non-product rebates
    marker := '0492';
    IF NVL(n_total_volume,1) <> 0 THEN
    n_non_prod_rebate := n_total_non_prod_rebate/NVL(n_total_volume,1)*c2.volume;
    ELSE
    n_non_prod_rebate := n_total_non_prod_rebate;
    END IF;

    IF DEBUG = 'Y' THEN
    dbms_output.put_line('vol '||c2.volume);
    dbms_output.put_line('port diff '||n_port_diff);
    dbms_output.put_line('pack diff '||n_pack_diff);
    END IF;

    -- insert records into test_waterfall fact
    marker := '0493';
    set_transaction_ref := set_transaction_ref;
    set_test_product_id := c2.del_test_product_id;
    set_test_account_id := c2.test_account_id;
    set_test_vessel_id := c2.test_vessel_id;
    set_test_location_id := c2.del_test_loc_id;
    set_test_stock_point_id := c2.test_stock_point_id;
    -- set_test_source_system_id := 1;
    set_test_source_system_id := NVL(c2.test_source_system_id,1); -- 1.9
    set_delivery_date := c2.del_date;
    set_invoice_date := c2.invoice_date;
    set_period_num_acctg := c2.period_num_acctg;
    set_created_date := SYSDATE;
    set_created_by := proc_name;
    set_updated_date := NULL;
    set_updated_by := NULL;
    set_vessel_name := c2.vessel_name;
    set_purchase_order_number := c2.purchase_order_number;
    set_manta_number := c2.manta_number;
    set_ord_num := c2.ord_num;
    set_manta_order_date := c2.manta_order_date;
    set_invoice_currency := c2.local_currency;
    set_exchange_rate_inv_to_local := 1;
    set_local_currency := c2.local_currency;
    set_exchange_rate_lcl_to_usd := c2.exchange_rate_to_rcu;
    set_contra_ind := c2.contra_ind;
    set_payment_due_date := c2.payment_due_date;
    set_ar_due_date := c2.ar_due_date;
    set_payment_date := c2.payment_date;
    set_business_type := c2.business_type;
    set_trade := c2.trade;
    set_ar_ind := c2.ar_ind;
    set_data_quality_ind := c2.data_quality_ind;
    set_contract_assignment := c2.contract_assignment;
    set_dlvy_note_num := c2.dlvy_note_num;
    set_distr_chan := NULL;
    set_division := c2.division;
    set_CREDITorg := NULL;
    SET_ORIGINAL_INVOICE_NUMBER := NULL; --1.8
    set_test_segment_id := c2.test_segment_id; -- 1.9

  6. #6
    Join Date
    Jun 2012
    Posts
    7
    IF v_invoice_with_product = 'Y' THEN
    IF DEBUG = 'Y' THEN
    dbms_output.put_line('product invoice');
    END IF;
    marker := '0500';
    IF c2.test_waterfall_element_id = 9999 THEN marker := '0600';
    BEGIN IF n_price_adjustment_sur <> 0 THEN
    marker := '0601';
    set_test_condition_type_id := 1;
    set_amount := n_price_adjustment_sur; insert_dw_CREDIT_fact;
    END IF;
    --port diff
    IF n_port_diff <> 0 THEN
    marker := '0602';
    set_test_condition_type_id := 2;
    set_amount := n_port_diff;
    insert_dw_CREDIT_fact;
    END IF;
    IF n_pack_diff <> 0 THEN
    marker := '0603';
    set_test_condition_type_id := 3;
    set_amount := n_pack_diff;
    insert_dw_CREDIT_fact;
    END IF;
    IF n_other_sur <> 0 THEN
    marker := '0604';
    set_test_condition_type_id := 4;
    set_amount := n_other_sur;
    insert_dw_CREDIT_fact;
    END IF;
    IF n_other_del_disc <> 0 THEN
    marker := '0605';
    set_test_condition_type_id := 5;
    set_amount := n_other_del_disc;
    insert_dw_CREDIT_fact;
    END IF;
    IF n_tech_offer <> 0 THEN
    marker := '0606';
    set_test_condition_type_id := 6;
    set_amount := n_tech_offer;
    insert_dw_CREDIT_fact;
    END IF;
    IF n_non_compliance_cred <> 0 THEN
    marker := '0607';
    set_test_condition_type_id := 7;
    set_amount := n_non_compliance_cred;
    insert_dw_CREDIT_fact;
    END IF;
    IF c2.inv_val <> 0 THEN
    marker := '0608';
    set_test_condition_type_id := 9999;
    set_amount := c2.inv_val;
    insert_dw_CREDIT_fact;
    END IF;
    --miscelleneous charges
    IF n_misc_charges <> 0 THEN
    marker := '0609';
    set_test_condition_type_id := 9;
    set_amount := n_misc_charges;
    insert_dw_CREDIT_fact;
    END IF;
    --other credit notes
    IF n_other_credits <> 0 THEN
    marker := '0610';
    set_test_condition_type_id := 10;
    set_amount := n_other_credits;
    insert_dw_CREDIT_fact;
    END IF;
    --price adjustment discount
    IF n_price_adj_disc <> 0 THEN
    marker := '0611';
    set_test_condition_type_id := 11;
    set_amount := n_price_adj_disc;
    insert_dw_CREDIT_fact;
    END IF;
    --amortisation
    IF c2.amortisation <> 0 THEN
    marker := '0612';
    set_test_condition_type_id := 12;
    set_amount := c2.amortisation;
    set_investment_id := c2.test_ivr_investment_id; -- 1.5 change
    insert_dw_CREDIT_fact;
    END IF;
    --cogs
    IF c2.cogs <> 0 THEN
    marker := '0613';
    set_test_condition_type_id := 13;
    set_amount := c2.cogs;
    insert_dw_CREDIT_fact;
    END IF;
    --discount
    IF c2.discount <> 0 THEN
    marker := '0614';
    set_test_condition_type_id := 14;
    set_amount := c2.discount;
    insert_dw_CREDIT_fact;
    END IF;
    --rebate
    IF c2.rebate <> 0 THEN
    marker := '0615';
    set_test_condition_type_id := 15;
    set_amount := c2.rebate;
    set_rebate_id := c2.test_ivr_rebate_id; -- 1.5 change
    insert_dw_CREDIT_fact;
    END IF;
    --non-product rebate
    IF n_non_prod_rebate <> 0 THEN
    marker := '0615';
    set_test_condition_type_id := 15;
    set_amount := n_non_prod_rebate;
    set_rebate_id := c2.test_ivr_rebate_id; -- 1.5 change
    insert_dw_CREDIT_fact;
    END IF;
    --logistics
    IF c2.logistics <> 0 THEN
    marker := '0616';
    set_test_condition_type_id := 16;
    set_amount := c2.logistics;
    insert_dw_CREDIT_fact;
    END IF;
    --cap adjustments
    IF c2.cap_adjustment <> 0 THEN
    marker := '0617';
    set_test_condition_type_id := 17;
    set_amount := c2.cap_adjustment;
    set_investment_id := c2.test_ivr_investment_id; --1.6 Change
    insert_dw_CREDIT_fact;
    END IF;
    --COSA
    IF c2.cosa <> 0 THEN
    marker := '0618';
    set_test_condition_type_id := 18;
    set_amount := c2.cosa;
    insert_dw_CREDIT_fact;
    END IF;
    --volume
    IF c2.volume <> 0 THEN
    marker := '0619';
    set_test_condition_type_id := 98;
    set_amount := c2.volume;
    insert_dw_CREDIT_fact;
    -- 1.3 insert_cnt := insert_cnt + 1;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Failed at marker:'|| marker || ' with error '||SQLERRM);
    dbms_output.put_line('Failed to insert test_dw_CREDIT_fact for transaction: '||set_transaction_ref);
    END;
    ELSE -- non-product line on a product invoice could have a cap adjustment or a discount or a rebate theoretically at least
    BEGIN
    --for investment releases it is possible to have a cap adjustment on a non-product line so insert it
    IF c2.cap_adjustment <> 0 THEN
    marker := '0700';
    set_test_product_id := 0;
    set_test_condition_type_id := 17;
    set_amount := c2.cap_adjustment;
    set_investment_id := c2.test_ivr_investment_id; --1.6 change
    insert_dw_CREDIT_fact;
    END IF;
    -- insert invoice value for cap adjustments coded as 17
    IF c2.test_waterfall_element_id = 17 AND c2.inv_val <> 0 THEN
    marker := '0701';
    set_test_product_id := 0;
    set_test_condition_type_id := 9999;
    set_amount := c2.inv_val;
    insert_dw_CREDIT_fact;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Failed at marker:'|| marker || ' with error '||SQLERRM);
    dbms_output.put_line('Failed to insert test_dw_CREDIT_fact for transaction: '||set_transaction_ref);
    END;
    END IF;
    ELSE -- invoice with no products so insert every invoice line
    BEGIN
    IF DEBUG = 'Y' THEN
    dbms_output.put_line('non product invoice');
    END IF;
    -- amortisation that has not been allocated to a product
    IF c2.test_waterfall_element_id = 12 AND c2.amortisation <> 0 THEN
    marker := '0702';
    set_test_product_id := 0;
    set_test_condition_type_id := 12;
    set_amount := c2.amortisation;
    set_investment_id := c2.test_ivr_investment_id; --1.6 change
    insert_dw_CREDIT_fact;
    END IF;
    -- rebates
    IF c2.rebate <> 0 THEN
    marker := '0705';
    set_test_product_id := 0;
    set_test_condition_type_id := 15;
    set_amount := c2.rebate;
    set_rebate_id := c2.test_ivr_rebate_id; --1.6 change
    insert_dw_CREDIT_fact;
    END IF;
    -- cap adjustment invoice line so insert one record for the cap adjustment and one record for the invoice value
    --if c2.test_waterfall_element_id = 17 and c2.cap_adjustment <> 0 then
    IF c2.cap_adjustment <> 0 THEN
    marker := '0710';
    set_test_product_id := 0;
    set_test_condition_type_id := 17;
    set_amount := c2.cap_adjustment;
    set_investment_id := c2.test_ivr_investment_id; --1.6 change
    insert_dw_CREDIT_fact;
    END IF;
    -- insert invoice value for cap adjustments coded as 17
    IF c2.test_waterfall_element_id = 17 AND c2.inv_val <> 0 THEN
    marker := '0720';
    set_test_product_id := 0;
    set_test_condition_type_id := 9999;
    set_amount := c2.inv_val;
    insert_dw_CREDIT_fact;
    END IF;
    -- any other invoice lines (including any cap adjusted invoices not coded as 17
    IF c2.test_waterfall_element_id <> 17 AND c2.inv_val <> 0 THEN
    marker := '0730';
    set_test_product_id := 0;
    set_test_condition_type_id := c2.test_waterfall_element_id;
    set_amount := c2.inv_val;
    insert_dw_CREDIT_fact;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Failed at marker:'|| marker || ' with error '||SQLERRM);
    dbms_output.put_line('Failed to insert test_dw_CREDIT_fact for transaction: '||set_transaction_ref);
    END;
    END IF;
    --1.5 start of changes
    set_rebate_id:=NULL;
    set_investment_id:=NULL;
    --1.5 End of changes
    END LOOP;
    marker := '0800';

    commit_cnt := commit_cnt + 1;
    IF MOD(commit_cnt, 500) = 0 THEN
    COMMIT;
    END IF;

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    since we do not have your tables or data, we can not run, test or improve posted code.

    consider using SQL_TRACE to see where actual time is being spent
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Its very difficult to read the code but it looks like you are executing a cursor and looping therough the result set looking at some columns and then inserting a record. Its usually much better to just do an insert..select.. so it inserts all of the rows in a single insert statement. Also I suspect you can put all of your if then logic into the sql as well but it is a bit difficult to understand it.

    Alan

  9. #9
    Join Date
    Feb 2005
    Posts
    57

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •