Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Adrian, Mar 8, 2018
- --! Jika terjadi perubahan, sesuaikan juga r_limit_detail_mou !
- CREATE OR REPLACE FUNCTION r_limit_detail_mou_for_salesman_csv(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pRefDetailGrandTotal REFCURSOR := 'refDetailGrandTotal';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pOuId ALIAS FOR $6;
- pPartnerId ALIAS FOR $7;
- pPeriodFrom ALIAS FOR $8;
- pPeriodTo ALIAS FOR $9;
- vPartnerCodeMou character varying(50);
- vPartnerNameMou character varying(100);
- vDocTypeIdSOByBrand bigint;
- vStatusReleased character varying(1);
- vStatusFinal character varying(1);
- vStatusInProgress character varying(1);
- vStatusDraft character varying(1);
- vTagKeyMOU character varying(10);
- vDocTypeIdReturnNote bigint;
- vDocTypeIdSalesInvoice bigint;
- vDocTypeIdInvoiceTemp bigint;
- vDocTypeIdConversionOfExchangeIn bigint;
- vDocTypeIdConversionOfExchangeOut bigint;
- vDocTypeIdRRS bigint;
- vStatusMapping character varying(1);
- vStatusApproved character varying(1);
- vRaw character varying(20);
- vSummary character varying(20);
- vEmptyId bigint;
- vOne integer := 1;
- vEmptyValue character varying;
- vParamCodeInsentif character varying := 'INCENTIVE';
- vActivityGLCodeInsentif character varying;
- vActivityGLInsentif bigint;
- vFlagYes character varying(1) :='Y';
- vDocNoMou character varying(50);
- i bigint;
- BEGIN
- vPartnerCodeMou := ' ';
- vPartnerNameMou := ' ';
- vDocTypeIdSOByBrand := 398;
- vStatusReleased := 'R';
- vStatusFinal := 'F';
- vStatusInProgress := 'I';
- vStatusDraft := 'D';
- vTagKeyMOU := 'MOU';
- vDocTypeIdReturnNote := 502;
- vDocTypeIdSalesInvoice := 321;
- vDocTypeIdInvoiceTemp := 361;
- vDocTypeIdConversionOfExchangeIn := 394;
- vDocTypeIdConversionOfExchangeOut := 395;
- vDocTypeIdRRS :=381;
- vStatusMapping := 'M';
- vStatusApproved := 'A';
- vRaw := 'RAW';
- vSummary := 'SUMMARY';
- vEmptyId := -99;
- vEmptyValue := '';
- vDocNoMou := '';
- i := 1;
- SELECT partner_code INTO vPartnerCodeMou
- FROM m_partner
- WHERE partner_id = pPartnerId;
- SELECT partner_name INTO vPartnerNameMou
- FROM m_partner
- WHERE partner_id = pPartnerId;
- -- Get data-data activity gl code incentive dari system config value
- SELECT TRIM(REPLACE(f_get_value_system_config_by_param_code(pTenantId, vParamCodeInsentif), ';', ''',''')) INTO vActivityGLCodeInsentif;
- /*
- --get data activity gl insentif
- SELECT B.parameter_value INTO vActivityGLCodeInsentif
- FROM t_parameter A
- INNER JOIN t_system_config B ON A.parameter_id = B.parameter_id
- WHERE A.parameter_code = vParamCodeInsentif
- AND B.tenant_id = pTenantId;
- --get activity gl
- SELECT activity_gl_id INTO vActivityGLInsentif
- FROM m_activity_gl
- WHERE tenant_id = pTenantId
- AND activity_gl_code = vActivityGLCodeInsentif;
- */
- --Menghapus semua table temporrer untuk mulai insert data baru
- DELETE FROM tt_sl_r_limit_detail_mou_list WHERE session_id = pSessionId;
- DELETE FROM tt_sl_mou_list WHERE session_id = pSessionId;
- DELETE FROM tt_sl_so WHERE session_id = pSessionId;
- DELETE FROM tt_sl_outstanding_so WHERE session_id = pSessionId;
- DELETE FROM tt_sl_r_limit_detail_mou_list_for_csv_detail WHERE session_id = pSessionId;
- -- Mengambil data MOU sesuai dengan filter nya
- INSERT INTO tt_sl_mou_list(
- session_id ,mou_id,tenant_id ,ou_id ,doc_type_id ,doc_date,doc_no,period_from ,period_to ,partner_id ,
- curr_code ,plafon_amount ,status_doc ,workflow_status)
- SELECT pSessionId ,mou_id,tenant_id ,ou_id ,doc_type_id ,doc_date,doc_no,period_from ,period_to ,partner_id ,
- curr_code ,plafon_amount ,status_doc ,workflow_status
- FROM sl_mou
- WHERE tenant_id = pTenantId
- AND ou_id = pOuId
- AND partner_id = pPartnerId
- AND period_from BETWEEN pPeriodFrom AND pPeriodTo
- AND status_doc IN (vStatusReleased,vStatusFinal)
- AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, ou_id) = vOne
- AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, partner_id) = vOne;
- -- Mengambil semua data SO By Brand yang tagging ke mou (semua status doc)
- INSERT INTO tt_sl_so(
- session_id ,so_id,tenant_id ,ou_id ,doc_type_id ,doc_date ,doc_no ,partner_id , mou_id, curr_code ,
- amount, amount_tax, status_doc ,workflow_status)
- SELECT pSessionId ,A.so_id,A.tenant_id ,A.ou_id ,A.doc_type_id ,A.doc_date,A.doc_no,A.partner_id ,C.mou_id, A.curr_code ,
- SUM(D.nett_brand_amount) AS amount, SUM(D.qty*D.tax_price) AS amount_tax ,A.status_doc ,A.workflow_status
- FROM sl_so A
- INNER JOIN sl_so_info B ON A.so_id = B.so_id
- INNER JOIN tt_sl_mou_list C ON B.mou_id = C.mou_id AND C.session_id = pSessionId
- INNER JOIN sl_so_brand_item D ON A.so_id = D.so_id
- WHERE A.tenant_id = pTenantId
- AND A.ou_id = pOuId
- AND A.doc_type_id = vDocTypeIdSOByBrand
- AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
- AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, A.ou_id) = vOne
- AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = vOne
- AND NOT EXISTS (
- SELECT 1 FROM sl_so_balance_invoice Z
- WHERE Z.so_id = A.so_id
- GROUP BY Z.so_id
- HAVING SUM(Z.qty_dlv_so) <= 0
- )
- GROUP BY A.so_id,A.tenant_id ,A.ou_id ,A.doc_type_id ,A.doc_date,A.doc_no,A.partner_id ,C.mou_id,A.curr_code ,
- A.status_doc ,A.workflow_status;
- -- Insert data so by brand ke table tt_sl_outstanding_so yang mana sudah tagging mou dan disubmit , status doc nya (R/F)
- INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id ,amount_so ,amount_tax_so)
- SELECT pSessionId, vRaw, A.so_id, A.amount, A.amount_tax
- FROM tt_sl_so A
- INNER JOIN sl_so_tagging B ON A.so_id = B.so_id AND B.tag_key = vTagKeyMOU
- WHERE A.session_id = pSessionId
- AND A.status_doc IN (vStatusReleased,vStatusFinal);
- -- Insert data invoice temp ke table tt_sl_outstanding_so yang ref_id (dokumen so by brand) tagging ke mou
- INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id,invoice_temp_id ,amount_inv_temp ,amount_tax_inv_temp)
- SELECT pSessionId, vRaw, B.ref_id,B.invoice_temp_id, B.total_amount, COALESCE(C.tax_amount,0)
- FROM tt_sl_outstanding_so A
- INNER JOIN sl_invoice_temp B ON A.so_id = B.ref_id
- LEFT JOIN sl_invoice_temp_tax C ON B.invoice_temp_id = C.invoice_temp_id
- WHERE A.session_id = pSessionId
- AND B.tenant_id = pTenantId
- AND B.ou_id = pOuId
- AND B.status_doc = vStatusReleased
- AND B.doc_type_id = vDocTypeIdInvoiceTemp
- AND NOT EXISTS (SELECT 1 FROM sl_invoice D
- WHERE D.doc_no = B.inv_doc_no
- AND D.ref_id = B.ref_id
- AND D.doc_type_id = vDocTypeIdSalesInvoice
- AND D.status_doc = vStatusReleased);
- -- Insert data invoice ke table tt_sl_outstanding_so yang ref_id (dokumen so by brand) tagging ke mou
- INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id,invoice_id ,amount_inv, amount_tax_inv)
- SELECT pSessionId, vRaw, B.ref_id, B.invoice_id, B.total_amount, COALESCE(D.tax_amount,0)
- FROM tt_sl_outstanding_so A
- INNER JOIN sl_invoice B ON A.so_id = B.ref_id
- INNER JOIN sl_invoice_tagging C ON B.invoice_id = C.invoice_id AND C.tag_key = vTagKeyMOU
- LEFT JOIN sl_invoice_tax D ON B.invoice_id = D.invoice_id
- WHERE A.session_id = pSessionId
- AND A.invoice_temp_id = vEmptyId
- AND B.tenant_id = pTenantId
- AND B.ou_id = pOuId
- AND B.status_doc = vStatusReleased
- AND B.doc_type_id = vDocTypeIdSalesInvoice;
- -- Insert ke table tt_sl_outstanding_so untuk menghitung summary (untuk mengetahui outstading amount nya so by brand)
- INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id,amount_so, amount_tax_so,
- amount_inv, amount_tax_inv, amount_inv_temp,
- amount_tax_inv_temp, final_so,
- final_tax_so)
- SELECT pSessionId, vSummary, so_id, SUM(amount_so) AS amount_so, SUM(amount_tax_so) AS amount_tax_so,
- SUM(amount_inv) AS amount_inv, SUM(amount_tax_inv) AS amount_tax_inv, SUM(amount_inv_temp) AS amount_inv_temp ,
- SUM(amount_tax_inv_temp) AS amount_tax_inv_temp ,(SUM(amount_so) - SUM(amount_inv) - SUM(amount_inv_temp)) AS final_so,
- (SUM(amount_tax_so) - SUM(amount_tax_inv) - SUM(amount_tax_inv_temp)) AS final_tax_so
- FROM tt_sl_outstanding_so
- WHERE session_id = pSessionId
- GROUP BY so_id;
- /*
- Insert data Invoice yang di mapping(di tarik manual ,sebelum nya SO by brand belum di tagging ke mou)
- dan status doc invoice = R -> ke table tt_sl_r_limit_detail_mou_list
- */
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
- status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax, total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, C.total_amount AS usage_amount,
- vStatusMapping AS status_doc_invoice, C.workflow_status, A.status_doc AS status_doc_mou,
- COALESCE(D.tax_amount, 0) AS usage_amount_tax, (C.total_amount + COALESCE(D.tax_amount, 0)) AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN sl_invoice_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
- INNER JOIN sl_invoice C ON B.invoice_id = C.invoice_id
- LEFT JOIN sl_invoice_tax D ON C.invoice_id = D.invoice_id
- WHERE A.session_id = pSessionId
- AND C.doc_type_id = vDocTypeIdSalesInvoice
- AND C.status_doc = vStatusReleased
- AND NOT EXISTS(SELECT 1 FROM tt_sl_so D WHERE D.session_id = pSessionId AND D.so_id = C.ref_id);
- -- Insert data Invoice (SO By Brand nya sudah di tagging ke MOU) dan status doc invoice = R -> ke table tt_sl_r_limit_detail_mou_list
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
- status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax, total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, C.total_amount AS usage_amount,
- vStatusApproved AS status_doc_invoice ,C.workflow_status, A.status_doc AS status_doc_mou,
- COALESCE(D.tax_amount, 0) AS usage_amount_tax, (C.total_amount + COALESCE(D.tax_amount, 0)) AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN sl_invoice_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
- INNER JOIN sl_invoice C ON B.invoice_id = C.invoice_id
- LEFT JOIN sl_invoice_tax D ON C.invoice_id = D.invoice_id
- WHERE A.session_id = pSessionId
- AND C.doc_type_id = vDocTypeIdSalesInvoice
- AND C.status_doc = vStatusReleased
- AND EXISTS(SELECT 1 FROM tt_sl_outstanding_so D
- WHERE D.session_id = pSessionId
- AND D.data_id = vRaw AND D.invoice_id = C.invoice_id AND D.so_id = C.ref_id);
- /*
- Insert data Invoice temp (SO By Brand nya sudah di tagging ke MOU) dan status doc invoice temp = R
- dan belum di convert ke invoice beneran yang mana status doc invoice beneran nya = R ke table tt_sl_r_limit_detail_mou_list
- */
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
- status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax, total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- D.invoice_temp_id AS doc_id, D.doc_type_id, D.doc_no, D.doc_date, D.partner_id, D.curr_code, D.total_amount AS usage_amount,
- D.status_doc AS status_doc_invoice_temp ,D.workflow_status, A.status_doc AS status_doc_mou,
- COALESCE(E.tax_amount, 0) AS usage_amount_tax, (D.total_amount + COALESCE(E.tax_amount, 0)) AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN sl_so_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
- INNER JOIN tt_sl_outstanding_so C ON B.so_id = C.so_id AND C.data_id = vRaw
- INNER JOIN sl_invoice_temp D ON C.invoice_temp_id = D.invoice_temp_id
- LEFT JOIN sl_invoice_temp_tax E ON D.invoice_temp_id = E.invoice_temp_id
- WHERE A.session_id = pSessionId
- AND C.session_id = pSessionId;
- -- Insert data SO By Brand yang sudah di tagging dan status doc( D dan I) ke table tt_sl_r_limit_detail_mou_list
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
- status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax, total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- B.so_id AS doc_id, B.doc_type_id, B.doc_no, B.doc_date, B.partner_id, B.curr_code, B.amount AS usage_amount,
- B.status_doc AS status_doc_so ,B.workflow_status, A.status_doc AS status_doc_mou,
- B.amount_tax AS usage_amount_tax, (B.amount + B.amount_tax) AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN tt_sl_so B ON A.mou_id = B.mou_id AND B.session_id = pSessionId
- WHERE A.session_id = pSessionId
- AND B.status_doc IN (vStatusDraft,vStatusInProgress);
- /* Insert data SO By Brand yang sudah di tagging dan status doc( R/F) ke table tt_sl_r_limit_detail_mou_list
- * UNTUK SO YG SUDAH SAMPAI DI INVOICE (STATUS R) TIDAK PERLU DIMUNCULKAN
- */
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
- status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax, total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- B.so_id AS doc_id, B.doc_type_id, B.doc_no, B.doc_date, B.partner_id, B.curr_code, B.amount AS usage_amount,
- B.status_doc AS status_doc_so ,B.workflow_status, A.status_doc AS status_doc_mou,
- B.amount_tax AS usage_amount_tax, (B.amount + B.amount_tax) AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN tt_sl_so B ON A.mou_id = B.mou_id AND B.session_id = pSessionId
- WHERE A.session_id = pSessionId
- AND B.status_doc IN (vStatusReleased,vStatusFinal)
- AND NOT EXISTS(SELECT 1 FROM sl_invoice_temp C WHERE B.so_id = C.ref_id AND C.status_doc = vStatusReleased)
- AND NOT EXISTS(SELECT 1 FROM sl_invoice D WHERE B.so_id = D.ref_id AND D.status_doc = vStatusReleased);
- /*
- * TIDAK LAGI MENAMPILKAN OUTSTANDING SO -> KARENA SO YG SUDAH DI INVOICE (R) TIDAK PERLU DIMUNCULKAN
- * KRN di KATAMATA 1 SO 1 INVOICE
- *
- -- Menghapus data dr table tt_sl_outstanding_so yang mana data_id = RAW
- DELETE FROM tt_sl_outstanding_so WHERE session_id = pSessionId AND data_id = vRaw;
- -- Insert data SO By Brand yang sudah di tagging ,status doc( R/F) ke table tt_sl_r_limit_detail_mou_list
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
- status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax, total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- D.so_id AS doc_id, D.doc_type_id, D.doc_no, D.doc_date, D.partner_id, D.curr_code, C.final_so AS usage_amount,
- D.status_doc AS status_doc_so ,D.workflow_status, A.status_doc AS status_doc_mou,
- C.final_tax_so AS usage_amount_tax, (C.final_so + C.final_tax_so) AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN sl_so_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
- INNER JOIN tt_sl_outstanding_so C ON B.so_id = C.so_id AND C.data_id = vSummary
- INNER JOIN tt_sl_so D ON C.so_id = D.so_id AND D.session_id = pSessionId
- WHERE A.session_id = pSessionId
- AND C.session_id = pSessionId;
- */
- --INSERT data return yang sudah di submit (status doc R/F) ke tt_sl_r_limit_detail_mou_list
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,
- usage_amount ,status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax,
- total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- C.inventory_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
- SUM(D.qty_realization * F.nett_sell_price) AS usage_amount,C.status_doc AS status_doc_return ,
- C.workflow_status, A.status_doc AS status_doc_mou,
- SUM(D.qty_realization * F.tax_price) AS usage_amount_tax,
- (SUM(D.qty_realization * F.nett_sell_price) + SUM(D.qty_realization * F.tax_price)) AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN in_return_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMOU
- INNER JOIN in_inventory C ON B.inventory_id = C.inventory_id
- INNER JOIN in_inventory_item D ON C.inventory_id = D.inventory_id
- INNER JOIN sl_request_return_sales E ON C.ref_id = E.request_return_sales_id AND C.ref_doc_type_id = vDocTypeIdRRS
- INNER JOIN sl_request_return_sales_brand_item F ON E.request_return_sales_id = F.request_return_sales_id
- AND f_get_brand_by_product_id(D.product_id) = F.brand_id
- INNER JOIN tt_sl_so G ON E.ref_id = G.so_id AND E.ref_doc_type_id = vDocTypeIdSOByBrand AND G.session_id = pSessionId
- WHERE A.session_id = pSessionId
- AND C.doc_type_id = vDocTypeIdReturnNote
- AND C.status_doc IN (vStatusReleased,vStatusFinal)
- GROUP BY A.tenant_id, A.ou_id, A.mou_id, A.doc_date , A.doc_no,A.period_from, A.period_to, A.partner_id , A.curr_code ,
- A.plafon_amount,C.inventory_id , C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
- C.status_doc ,C.workflow_status, A.status_doc;
- --INSERT data return yang belum di submit (status doc D/I) ke tt_sl_r_limit_detail_mou_list
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,
- usage_amount ,status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax,
- total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- C.inventory_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
- SUM(D.qty_realization * F.nett_sell_price) AS usage_amount,C.status_doc AS status_doc_return ,
- C.workflow_status, A.status_doc AS status_doc_mou,
- SUM(D.qty_realization * F.tax_price) AS usage_amount_tax,
- (SUM(D.qty_realization * F.nett_sell_price) + SUM(D.qty_realization * F.tax_price)) AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN tt_sl_r_limit_detail_mou_list B ON A.mou_id = B.mou_id AND B.doc_type_id = vDocTypeIdSOByBrand
- INNER JOIN sl_request_return_sales E ON B.doc_id = E.ref_id AND E.ref_doc_type_id = vDocTypeIdSOByBrand
- INNER JOIN sl_request_return_sales_brand_item F ON E.request_return_sales_id = F.request_return_sales_id
- INNER JOIN in_inventory C ON C.ref_id = E.request_return_sales_id AND C.ref_doc_type_id = vDocTypeIdRRS
- INNER JOIN in_inventory_item D ON C.inventory_id = D.inventory_id
- INNER JOIN tt_sl_so G ON E.ref_id = G.so_id AND E.ref_doc_type_id = vDocTypeIdSOByBrand AND G.session_id = pSessionId
- WHERE A.session_id = pSessionId
- AND B.session_id = pSessionId
- AND C.doc_type_id = vDocTypeIdReturnNote
- AND C.status_doc IN (vStatusDraft,vStatusInProgress)
- GROUP BY A.tenant_id, A.ou_id, A.mou_id, A.doc_date , A.doc_no,A.period_from, A.period_to, A.partner_id , A.curr_code ,
- A.plafon_amount,C.inventory_id , C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
- C.status_doc ,C.workflow_status, A.status_doc;
- -- Insert data MOU yang sama sekali belum digunakan ke table tt_sl_r_limit_detail_mou_list
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id, ou_id, mou_id, doc_date_mou, doc_no_mou,
- period_from, period_to, partner_id_mou, curr_code_mou, plafon_amount,
- doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, usage_amount,
- status_doc, workflow_status, status_doc_mou)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- vEmptyId, vEmptyId, vEmptyValue, vEmptyValue, A.partner_id, vEmptyValue, 0 AS usage_amount,
- vEmptyValue, vEmptyValue, A.status_doc AS status_doc_mou
- FROM tt_sl_mou_list A
- WHERE A.session_id = pSessionId
- AND A.mou_id NOT IN (SELECT DISTINCT B.mou_id FROM tt_sl_r_limit_detail_mou_list B WHERE B.session_id = pSessionId);
- /* Insert data CN AR non-insentif yang dimapping ke MOU */
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
- status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax, total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, SUM(D.add_amount)*-1 AS usage_amount,
- vStatusMapping AS status_doc_so, C.workflow_status, A.status_doc AS status_doc_mou,
- SUM(D.tax_amount)*-1 AS usage_amount_tax, (SUM(D.add_amount) + SUM(D.tax_amount))*-1 AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN fi_invoice_ar_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMOU
- INNER JOIN fi_invoice_ar C ON B.invoice_ar_id = C.invoice_ar_id
- INNER JOIN fi_invoice_ar_cost D ON C.invoice_ar_id = D.invoice_ar_id
- WHERE A.session_id = pSessionId
- AND C.status_doc IN (vStatusReleased,vStatusFinal)
- AND C.doc_type_id = 251
- AND f_is_invoice_ar_cost_unvalid_by_activity_gl_code(C.invoice_ar_id, vActivityGLCodeInsentif, A.tenant_id) = vFlagYes
- GROUP BY A.tenant_id, A.ou_id, A.mou_id, A.doc_date, A.doc_no,
- A.period_from, A.period_to, A.partner_id, A.curr_code, A.plafon_amount,
- C.invoice_ar_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code,
- C.workflow_status, A.status_doc;
- /* Insert data DN AR yang dimapping ke MOU */
- INSERT INTO tt_sl_r_limit_detail_mou_list(
- session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
- period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
- doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
- status_doc ,workflow_status ,status_doc_mou,
- usage_amount_tax, total_usage_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
- A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
- C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, C.total_amount AS usage_amount,
- vStatusMapping AS status_doc_so, C.workflow_status, A.status_doc AS status_doc_mou,
- C.tax_amount AS usage_amount_tax, (C.total_amount + C.tax_amount) AS total_usage_amount
- FROM tt_sl_mou_list A
- INNER JOIN fi_invoice_ar_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMOU
- INNER JOIN fi_invoice_ar C ON B.invoice_ar_id = C.invoice_ar_id
- WHERE A.session_id = pSessionId
- AND C.status_doc IN (vStatusReleased,vStatusFinal)
- AND C.doc_type_id = 241;
- Open pRefHeader FOR
- SELECT 12 AS _COUNT, 'LAPORAN LIMIT CUSTOMER (DETAIL)',
- '', '', '', '', '', '',
- '', '', '', '', '',
- 1 AS ordial
- UNION
- SELECT 12 AS _COUNT,
- '', '', '', '', '', '',
- '', '', '', '', '', '',
- 2 AS ordial
- UNION
- SELECT 12 AS _COUNT, 'OU', ': ' || f_get_ou_name(pOuId),
- '', '', '', '',
- '', '', '', '',
- 'PRINT DATE', ': ' || TO_CHAR(TO_TIMESTAMP(pDatetime, 'YYYYMMDDHH24MISS'), 'Day, DD Month, YYYY, HH:MI:SS'),
- 3 AS ordial
- UNION
- SELECT 12 AS _COUNT, 'PERIOD', ': ' || TO_CHAR(TO_DATE(pPeriodFrom,'YYYYMMDD'), 'DD/MM/YYYY') || ' s/d ' || TO_CHAR(TO_DATE(pPeriodTo,'YYYYMMDD'), 'DD/MM/YYYY'),
- '', '', '', '',
- '', '', '', '',
- 'USER – ROLE', ': ' || f_get_username(pUserId) || ' - ' || f_get_role_name(pRoleId),
- 4 AS ordial
- UNION
- SELECT 12 AS _COUNT, 'CUSTOMER', ': ' || vPartnerCodeMou || ' / ' || vPartnerNameMou,
- '', '', '', '',
- '', '', '', '', '', '',
- 5 AS ordial
- UNION
- SELECT 12 AS _COUNT,
- '', '', '', '', '', '',
- '', '', '', '', '', '',
- 6 AS ordial
- UNION
- SELECT 12 AS _COUNT,
- 'MOU No', 'MOU Date', 'Start Period', 'End Period',
- 'Nilai Paket', 'Doc No', 'Doc Date', 'Currency',
- 'Usage Amount', 'Sisa Plafon', 'Owner Code', 'Owner Name',
- 7 AS ordial
- ORDER BY ordial;
- RETURN NEXT pRefHeader;
- FOR vDocNoMou IN (
- SELECT A.doc_no_mou
- FROM tt_sl_r_limit_detail_mou_list A
- GROUP BY A.doc_no_mou
- ) LOOP
- PERFORM SETVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq', 0);
- INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
- session_id, doc_no_mou,
- doc_date_mou, period_from, period_to,
- curr_code_mou, plafon_amount,
- doc_no, doc_date,
- curr_code,
- total_usage_amount,
- remaining_plafon_amount, owner_code, owner_name,
- ordial, line_no)
- SELECT pSessionId, A.doc_no_mou,
- TO_CHAR(TO_DATE(A.doc_date_mou,'YYYYMMDD'), 'DD/MM/YYYY'), TO_CHAR(TO_DATE(A.period_from,'YYYYMMDD'), 'DD/MM/YYYY'), TO_CHAR(TO_DATE(A.period_to,'YYYYMMDD'), 'DD/MM/YYYY'),
- A.curr_code_mou, CAST(A.plafon_amount AS character varying) AS plafon_amount,
- A.doc_no, TO_CHAR(TO_DATE(A.doc_date,'YYYYMMDD'), 'DD/MM/YYYY'),
- A.curr_code,
- CAST((CASE WHEN A.doc_type_id = vDocTypeIdReturnNote THEN (A.total_usage_amount * -1)
- ELSE A.total_usage_amount
- END) AS character varying) AS total_usage_amount,
- '', B.partner_code AS owner_code, B.partner_name AS owner_name,
- i AS ordial, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq') AS line_no
- FROM tt_sl_r_limit_detail_mou_list A
- INNER JOIN m_partner B ON A.partner_id = B.partner_id
- WHERE A.session_id = pSessionId AND
- A.doc_no_mou = vDocNoMou
- --AND A.usage_amount <> 0
- ORDER BY A.doc_no_mou,B.partner_code, A.doc_date_mou, A.doc_no, A.doc_type_id, A.doc_date ASC ;
- INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
- session_id, doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
- plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
- remaining_plafon_amount, owner_code, owner_name, ordial, line_no)
- SELECT pSessionId, '', '', '', '', '',
- '', 'Sub Total', '',
- A.curr_code,
- CAST(SUM(A.total_usage_amount::numeric) AS character varying) AS total_usage_amount,
- CAST((A.plafon_amount::numeric - SUM(A.total_usage_amount::numeric)) AS character varying) AS remaining_plafon_amount,
- '', '',
- i AS ordial, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq') AS line_no
- FROM tt_sl_r_limit_detail_mou_list_for_csv_detail A
- WHERE A.session_id = pSessionId AND
- A.doc_no_mou = vDocNoMou
- --AND A.usage_amount <> 0
- GROUP BY A.doc_no_mou, A.curr_code, A.plafon_amount;
- INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
- session_id, doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
- plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
- remaining_plafon_amount, owner_code, owner_name, ordial, line_no)
- VALUES (pSessionId, '', '', '', '', '',
- '', '', '', '', '',
- '', '', '',
- i, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq'));
- i := i + 1;
- END LOOP;
- PERFORM SETVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq', 0);
- WITH tt_grouped_limit_mou AS(
- SELECT A.curr_code_mou,
- A.plafon_amount::numeric AS plafon_amount,
- A.curr_code,
- SUM(A.total_usage_amount::numeric) AS total_usage_amount
- FROM tt_sl_r_limit_detail_mou_list_for_csv_detail A
- WHERE A.session_id = pSessionId AND
- A.plafon_amount <> '' AND
- A.total_usage_amount <> ''
- --AND A.usage_amount <> 0
- GROUP BY A.curr_code_mou, A.curr_code, A.plafon_amount
- )
- INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
- session_id, doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
- plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
- remaining_plafon_amount, owner_code, owner_name, ordial, line_no)
- SELECT pSessionId, 'Grand Total', '', '', '', A.curr_code_mou,
- CAST(SUM(A.plafon_amount) AS character varying) AS plafon_amount, '', '',
- A.curr_code,
- CAST(SUM(A.total_usage_amount) AS character varying) AS total_usage_amount,
- CAST((SUM(A.plafon_amount) - SUM(A.total_usage_amount)) AS character varying) AS remaining_plafon_amount,
- '', '',
- i AS ordial, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq') AS line_no
- FROM tt_grouped_limit_mou A
- --AND A.usage_amount <> 0
- GROUP BY A.curr_code_mou, A.curr_code
- ORDER BY A.curr_code_mou, A.curr_code;
- Open pRefDetail FOR
- SELECT doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
- plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
- remaining_plafon_amount, owner_code, owner_name, ordial
- FROM tt_sl_r_limit_detail_mou_list_for_csv_detail A
- WHERE A.session_id = pSessionId
- ORDER BY A.ordial, A.line_no;
- RETURN NEXT pRefDetail;
- --Menghapus semua table temporrer
- DELETE FROM tt_sl_r_limit_detail_mou_list WHERE session_id = pSessionId;
- DELETE FROM tt_sl_mou_list WHERE session_id = pSessionId;
- DELETE FROM tt_sl_so WHERE session_id = pSessionId;
- DELETE FROM tt_sl_outstanding_so WHERE session_id = pSessionId;
- DELETE FROM tt_sl_r_limit_detail_mou_list_for_csv_detail WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement